Checking more than one table with VLOOKUP

The functions we will use are

• IF - A simple IF-function which has a logical validation, and two conditions, one for a true outcome, one for a false outcome. This is one of the most used and simplest functions in Excel
• ISNA - This is a function out of the "Information Bucket" of MSXL's function categories. It checks wether a value is #N/A or not and returns TRUE or FALSE
• VLOOKUP - Finally the core of our formula. As mentioned in other posts before, this function checks a target value against a matrix and then returns any given value of that matrix. It can have exact or "just" close matches. The difference between that has been explained in the articles mentioned above

Ok, let's go. Imagine, you have a workbook with three worksheets / tables like this:

Overview

East

West

Display Excel tables online >> Excel Jeanie HTML 4

What we want to do now is to return the correct state abrreviation of either the EAST or the WEST table into our Overview worksheet. The formula looks like this:

=IF(ISNA(VLOOKUP(A2,East!$A$1:$B$9,2,FALSE)),

VLOOKUP(A2,West!$A$1:$B$8,2,FALSE),

VLOOKUP(A2,East!$A$1:$B$9,2,FALSE))

Okay, okay, I know it. It looks terrible and totally "overcrowded". But I will explain it step by step so everyone will get a clue, how it works.

IF This simply allows us to use more than one table as target matrix for our VLOOKUP. In fact they are two. If we want more than two matrices to look in, we have to nest the IF functions into one another.

ISNA(VLOOKUP(A2,East!$A$1:$B$9,2,FALSE)) This informational function will check the following validation for a value TRUE or FALSE. In our case it says in plain English:

"If you have checked for the cities in our East-Coast-table and have found nothing, then please return the value TRUE, because the question do we get a "#N/A" value is answered with yes (=TRUE). Otherwise (you have found something in our East-Coast-table) answer is with NO (=FALSE) because the returned value is not "#N/A"."

VLOOKUP(A2,West!$A$1:$B$8,2,FALSE) - The TRUE part of the IF function Okay. The answer in the ISNA question (see above) was: "Yes, we have a "#N/A" value, therefore it's TRUE". That means, we have to switch to our next table, which is in our case the West-Coast-table. There we do our VLOOKUP looking for the cities (exact match) of the OVERVIEW-table and returning the state abbreviation (column 2 of our tarhet matrix)

VLOOKUP(A2,East!$A$1:$B$9,2,FALSE) - The FALSE part of the IF function So the answer of the ISNA question (see above) was FALSE, meaning, yes we have found our city in the East-Coast-table, then please proceed with the VLOOKUP for the East-Coast-table, meaning:

• take the city
• look in the matrix for an exact match
• return column 2 - the state

The result of the formula should be like this:

Overview

Display Excel tables online >> Excel Jeanie HTML 4

Once you got the concept of this formula it comes in very handy, because it allows you to look in several tables for a result. Some tips - which of course can also be applied to other formuals as well - on this method:

1. Take care of the braces. Always make sure to close the brackets
2. If you nest more than one IF-functions into this formula (I will post a video on how to do that in the near future), always have the matching result of the first VLOOKUP - the one that ISNA checks for - in the last argument of your initial or first IF function. This means that you should repeat the VLOOKUP of the ISNA in the last argument before the all brackets are closed.
3. Be sure to take care of your "regional settings". As mentioned by Art in the comments section below you have to the semicolon ";" as a separator if you're working with European versions of Excel and a comma "," if you have a non-European (i. e. US) version of the software. Thank you Art for the hint! :-)

What do you think? Is that useful for your daily works? Do you have ideas, where it can be utilized? Write a comment or send me an email through my contact form.

Discuss this blog post in the forums (click the blue button)