In this tutorial we will look at how we can use VLOOKUP with multiple criteria, but with the special twist that the choice of one criteria can come from different columns! Keep reading or watch the video and see how we use the functions of IF, ISNUMBER, VLOOKUP and SUMIFS to accomplish this feat!
This might be useful for you in a scenario where you want to pull sales data for a customer and state location by either their Customer Name or Customer Number. In our example, we will use student names or numbers for the selection.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
So, here we have a list of Student #, Name, Class, & Grade, and notice from the drop down list in cell F4 that I can choose either the student’s name or number as one of the criteria for the VLOOKUP function to pull their correct grade:
Now, if we were to only use the student’s name and class to pull the correct grade, we could use the following formula in cell H7:
Notice how I used the SUMIFS function to pull the grade number from column D. Now, we are not going to add anything up, but since there should only be one matching criteria of Name and Class, the correct value will result. Note also that we are using a “1” as the [range_lookup] in our VLOOKUP function because the match in the table in F9:G13 is an approximate match.
This formula will give us the right answer as long as a name is selected in cell F4, since it will look in cells B2:B13, the Name list, for a match. If we chose a number from the drop down list, we would get an #N/A error.
The same would occur if our formula in H7 was:
…and we chose a name in cell F4:
So if we want to have the option to choose either the name or number, we can introduce an IF function that will test whether F4 is a number or not, using an ISNUMBER function as the logical test:
=IF(ISNUMBER(F4),value if true,value if false)
Now all we need to do is insert the VLOOKUP statement above looking for a number as the “value if true” condition, and the other VLOOKUP statement above looking for a name as the “value if false” argument, like this:
And now, if we choose a number, it will use the first VLOOKUP formula, and if not, the second will be initiated:
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!