VLOOKUP on multiple tables

November 14, 2009 at 23:16:38
Specs: Windows Vista
I have a coursework assignment to do. In my spreadsheet, i have 2 sheet with information on games and accessories. I want to put a vlookup in my invoice sheet which searches for the item code in A1 (item codes in the 2 sheets), and look for its corresponding item name. i also want to add the ISNA bit, so that if there is no item code entered, it would not come up with #NA or #VALUE. I tried this for only 1 table and it worked, but i dont know how to do 2 -

=IF(ISNA(VLOOKUP(A14,Games, 2, FALSE)),"",VLOOKUP(A14,Games,2, FALSE)).

See More: VLOOKUP on multiple tables

Report •

November 15, 2009 at 09:10:11
In this forum, we don't usually answer homework questions directly, since the object is for you to figure how to use Excel to complete the assignment.

With that spirit in mind, I'll offer a process for finding a solution. If you can't figure it out, come on back and we'll see if we can give you some more hints.

I am assuming that you have 2 tables, which I'll call Games and Games2. You want to look up the value in A14, first in Games and then in Games2. If it is not found in either table, return "". If it is found, then return the value from Column 2 of the first table it is found in.

You've got a good start with your single table lookup formula.

What I would try for the 2 table lookup is to start by entering the first IF, as above and then deciding what I want to do if the first IF (the ISNA(Games)) is TRUE. I don't want a "" yet because I haven't checked Games2. So I would then check Games2 for an ISNA and then return "" since both ISNA's would be TRUE if the value wasn't found in either table.

If the Games ISNA was TRUE, but the Games2 ISNA was FALSE, then the value must have been found in Games2, so next I'd do a VLOOKUP on Games2.

If both ISNA's were FALSE, the value must be in Games, so I'd finish up with a VLOOKUP on Games.

It's sort of an "first-second-second-first" logic path.

Good luck!

Report •

November 17, 2009 at 06:19:58
how would you write that out in the formula tho? im kind of confused :S

Report •

November 17, 2009 at 06:44:52
The syntax for an If statment is:

IF(logical_test, value_if_true, value_if_false)

In your case, which is a version of a Nested IF, the value_if_true for the first logical_test would be another IF:


Both of these will be your ISNA(VLOOKUP)'s

Then you fill in the value_if_true and value_if_false for logical_test2:

value_if_test2_true, value_if_test2_false),

Then finally, the value_if_false for logical_test1


Just make sure you get your parenthese in the right places!

Report •

Related Solutions

November 17, 2009 at 06:45:39

i tried doing that. but it didnt work.

Report •

November 17, 2009 at 06:48:18
OH WAIT. SORRY. whoops i just realised i put the last bit the wrong way round. Thanks for your help :)

Report •

November 17, 2009 at 12:50:45
You got it...assuming you swapped the last 2 arguments.

Report •

Ask Question