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.

[…]

wrong, sum, number, average, excel, status, bar

Status Bar Shows a Wrong Result (Sum, Average) – Here Is Why

Excel has a very handy feature: It displays some quick information about the selected cells in the status bar. This might be the sum, average, maximum or minimum value. Unfortunately, in some cases, the shown number seems to be wrong. Here is why!

[…]

exchange, rates, excel, currency, currencies, conversion

Currency Conversion in Excel: Tutorial + Free Excel Add-In

In today’s business world, there is hardly any Excel workbook not  using currencies and exchange rates. Example: When I want to know my revenue for the last month – let’s say of my Excel add-ins or books – I receive an Excel file containing data in various currencies. Because I’m located in Germany, I want to convert everything to Euro. That’s when the trouble starts: Which exchange rate do I use for example for converting US Dollar to Euro? And more importantly: Where do I get the currency exchange rate in a convenient and fast way?

[…]

bullet, points, excel

Bullet Points in Excel: 6 Easy Methods (+Download)

No matter if you use Microsoft Word, PowerPoint, Outlook or OneNote: You can easily insert bullet points and create bullet point lists. In Excel you can’t. But there are some simple workarounds. In this article, you learn 6 methods for inserting bullet points in Excel. 

[…]

Text Import Wizard Gone? How to Import CSV and Text Files in Excel

Microsoft quietly replaced the comfortable Text Import Wizard from Excel and replaced it with the “Get & Transform” tools. The “Get & Transform” tools offer a lot of options and are very powerful. Unfortunately, they are quite complicated to use. Here is what you should now.

[…]