I can safely assume that almost everyone knows about LOOKUP formulas or precisely VLOOKUP. That is probably because sights and sounds of Excel don’t go without shouts and screams of lookup formulas. Learning Excel and not learning lookup formulas is like learning Japanese and not learning how to use chop sticks!
So if you are new to VLOOKUP then head over to: How Excel VLOOKUP Works: How to use it – Explanation with Example. This article explains the concept behind lookup formulas and how to write your first ever VLOOKUP formula.
Usually we give one value to the excel that acts as a criteria and tell to find the corresponding match in a specific column. But what if we have two criteria to tell instead of one. This is what 2-way lookup is. Consider the following data:
Lets say I want Excel to tell me how many classes of Tax are done in the month of March or January. We can do this visually too as we can see Audit classes in the month of March are just 1. But assume you have a mountain of data that is as wide as equator of the earth and as deep as ocean. Well I am just kidding!
So how to do it with a formula?
I already have the data validation lists setup in cell B9 and B10 for Month and Subject respectively. From the drop down you can change the month and subject. So by now you must have understood that we are trying to find the number of classes based on two criteria i.e. Subject and the Month. Following animation will help you make data validated drop down lists:
Repeat the same steps to get the drop down list for subject by selecting the first column as the range.
Step 1: Select the range including the header row and column and hit Ctrl+Shit+F3. This will invoke create names from selection dialogue and will let you select the options to name the ranges. Make sure top and left column is ticked with others unticked and press OK.
Step 2: In cell B12 put this formula:
=INDIRECT(B9) INDIRECT(B10)
Two things that require attention:
- We are using INDIRECT function to convert the text input to reference as for example Jan is a month name and also a name of range B2:B7. INNDIRECT() function helps us get that range easily.
- There is a SPACE between two INDIRECT formulas.
That is actually intersect operator at work. So we are asking Excel to fetch the value where the two ranges i.e. first range pulled by INDIRECT(B9) which is of Month and second range pulled by INDIRECT(B10) which is of subject.
Following animation will help you put the formula in place:
But we run into problems with this formula if we select Financial Management subject from the drop down list. The reason is names cannot have spaces and thus at the time names were defined, names manager must have replaced space between Financial and Management with an underscore.
To correct this problem replace the formula we have in cell B12 with this one:
=INDIRECT(B9) INDIRECT(SUBSTITUTE(B10,” “,”_”))
It is exactly the same formula as we had in Step 2 but with an addition of SUBSTITUTE() function that is telling Excel to substitute spaces in text of cell B10 with underscores.
So what do you learn in this tutorial:
- doing two-way lookup and beyond one-way lookup
- Creating names with selection super fast
- Using SUBSTITUTE() function to replace parts of text
- INDIRECT() function to make text as active references.