Professor Excel
Case-Sensitive Lookups in Excel

Case-Sensitive Lookups in Excel: 4 Methods (+XLSX-Download)

By definition, the VLOOKUP formula is not case-sensitive. Case-sensitive means, that it matters if you use capital letters or small letters. For instance, a VLOOKUP search for “AAA” will return the same value as for “aaa” or “Aaa”. But in some cases, you want to differentiate between capital and small letters. So how do you proceed? In this article, you learn how to make VLOOKUP, HLOOKUP, INDEX/MATCH and SUMIFS case-sensitive.

[…]

VLOOKUP to the Left

VLOOKUP to the Left in Excel. Yes, It’s Possible!

The VLOOKUP formula in its base version only works from left to right. The search column must be located on the left-hand side of the return column. What if your data doesn’t have such structure? There is a way for using the VLOOKUP to the left but it requires an array form of the formula. It’s often worth considering alternative formulas though. Here is everything you should know.

[…]

Multi-Condition Lookups in Excel

Multi-Condition VLOOKUP and INDEX/MATCH (+Excel-Download)

There are many cases in which you want to conduct a lookup with several search criteria. As of now only the SUMIFS formula allows a multi-condition lookup. Unfortunately, SUMIFS only works for numeric values (including dates) as the return value. If you want to return text, there is no direct method.

[…]

choose, formula, excel

CHOOSE Formula in Excel: Everything You Should Know

The CHOOSE formula in Excel is one of those, which is quite unknown. It can be very helpful though: For example it can easily convert the weekday-number into the weekday name. Often, it is used within other formulas. In this article you learn everything you need to know about the formula.

[…]

Combine Text in Excel

Combine Text in Excel: 4 Best Ways (+Download)

Excel offers three distinct formulas as well as a fourth way to combine multiple text cells into one cell. There are countless examples in which you might need this: Combine given– and family names or preparing primary keys for multi-conditional lookups. For example, in a VLOOKUP or INDEX/MATCH formula combination. In this article you learn 4 methods and in the end, you learn how to deal with a large range of cells.

[…]

find, search, find(), search(), findb, searchb, formula, formulas

FIND & SEARCH in Excel: How and When to Use These Formulas

The two formulas FIND and SEARCH in Excel are very similar. They search through a cell or some text for a keyword or character. Once found, they return the number of characters, at which the keyword starts. Let’s learn how to use them and explore the differences of the two formulas.

[…]

substitute, formula, excel

How to Replace Text with the SUBSTITUTE Formula in Excel

Sometimes, you need to change text in a systematic way: If you want to replace some text with new text, there are two options. If you only want to do it once, using the Find-and-Replace dialogue is probably the fastest choice. If you want to do it repeatedly or don’t want to mess with your input data, you should try the SUBSTITUTE formula.

lookup, formula, excel

LOOKUP: Everything You Should Know about the Very Unknown Excel Formula

Besides the well-known VLOOKUP formula, there is a similar formula in Excel: LOOKUP. The formula is very unkown and hardly used. But there are good reasons why you should not use the LOOKUP formula. Let’s start with the basics about the formula and then talk about why you should avoid it.

[…]

formulas, dictionary, excel, language, languages

Dictionary for Excel Formulas

Microsoft Excel supports many languages. In total, you can use Excel in 107 different languages. Many of these languages also use local formula names. For example, VLOOKUP is called SVERWEIS in German, BUSCARV in Spanish or DÜŞEYARA in Turkish. Fortunately, you don’t need to remember different formula names because Excel automatically translates the formulas to your language. However, in some cases you need to know the local formula term. Please refer to our comprehensive dictionary below.

[…]

len, formula, length, excel

LEN Formula: Get the Number of Characters in Excel

You need to know how many characters does the cell contain?