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.

[…]

3d, 3 way, lookups, 3d lookups, excel

3D Lookups in Excel: 6 Methods (+Download)

The king of lookups in Excel: The 3 way- or 3D lookup. Imagine this scenario: You got several Excel tables, each has rows and columns. Depending on your input values, you want to get the data from a specific cell from the right table, row and column. Such lookups are called 3D lookups or 3 way lookups. In this article we explore 6 methods of how to conduct 3D lookups in Excel.

[…]

2D Lookups in Excel: 4 Easy Methods for 2 Way Lookups + Excel Download

There are many cases in which you need a 2 dimensional lookup. That means, if you want to get a value from a specific row-column combination with neither rows or columns fixed. Unfortunately, the problem of a two way lookup comes up quite often. In this article we explore 4 methods of how to conduct 2D lookups in Excel.

[…]

vlookup, hlookup, lookup, excel, how, guide

VLOOKUP in Excel: All You Need to Know About the Powerful Formula

One of the most popular formulas in Excel is the VLOOKUP formula. Many lookup approaches are based on the VLOOKUP formula. Mastering it is crucial for any of the following chapters and methods. Unfortunately, VLOOKUP is not as easy to use as a SUM or COUNT. In this article, you learn how to use VLOOKUP, what to keep in mind and some more advices about VLOOKUP.
[…]

index, match, excel, formula, vlookup

INDEX and MATCH: The Alternative to VLOOKUP in Excel

You’ve probably heard of the VLOOKUP formula in Excel, haven’t you? The VLOOKUP formula searches for a value in a column. Once found it returns another value from the same row. A combination of INDEX and MATCH serves the same purpose. It works slightly different and has therefore some advantages and disadvantages towards VLOOKUP. 

[…]

When to use VLOOKUP, SUMIFS or INDEX/MATCH in Excel

One of the most often used functions when creating an Excel model is consolidating data from different sources. There are 3 major formulas for combining data from different tables or worksheets: VLOOKUP, SUMIFS and INDEX/MATCH. VLOOKUP and SUMIFS are rather popular whereas INDEX/MATCH is usually not that well known. So what is the difference between these 3 formulas and which one should you use? […]