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)).

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

processfor 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

thenreturn "" 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!

how would you write that out in the formula tho? im kind of confused :S

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:

IF(logical_test1,IF(logical_test2,

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

value_if_test1_false)

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

=IF(ISNA(VLOOKUP(A12,Games,2,FALSE)),IF(ISNA(VLOOKUP(A12,Accessories,2,FALSE)),"",VLOOKUP(A12,Games,2,FALSE)),VLOOKUP(A12,Accessories,2,FALSE)) i tried doing that. but it didnt work.

OH WAIT. SORRY. whoops i just realised i put the last bit the wrong way round. Thanks for your help :)

You got it...assuming you swapped the last 2 arguments.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History