Hello,

I have specific question about Excel and will try to explain. In one database have to search items by "MasterCode" and to return some date with VLOOKUP. If something is missing or it can't be found by MasterCode, I have also ItemCode for every item and the same Database. So my question is - Is there a possibility if the formula (Vlookup) did't find the data by MasterCode to continue searching by Itemcode. NOTE that some of the Master and Item Code are equal. To make it more clear -

MasterCode ItemCode

ITEM A 123 234

ITEM B 345 345

In the Database where I seach for maching by MasterCode to each Item i have the both codes - Master and Item. If it's not clear please let me know. Thanks in advance.

First, a posting tip: Before posting any more data in this forum, please click on the blue lineat the bottom of this post and read the instructions found via that link.Now, in answer to your question...

You can "nest" VLOOKUP's by using the ISNA function, since the #N/A error what is returned when a VLOOKUP function can't find what it is looking for...but first a few key points to consider.

As I'm sure you know, the VLOOKUP function searches for the

lookup_valuein thefirstcolumn of thetable_array. Since the ItemCode is not in the same column as the MasterCode, you can't use the same reference for the 2table_arrays. You'll have to use a differenttable_arrayreference and therefore a differentcol_index_numberfor the ItemCode VLOOKUP.So, let me post a suggestion and then break it down for you:

First, as a means to explain how the ISNA function can be used, let's look at a simple VLOOKUP that uses the ISNA function to determine if the

lookup_valueis found, and then either returns the value from thecol_index_numspecified or a message saying the value wasn't found:=IF(ISNA(VLOOKUP(A1, $B1:$C$5, 2, 0)), "Value Not Found", VLOOKUP(A1, $B1:$C$5, 2, 0))

ISNA returns TRUE if the VLOOKUP returns #N/A (value not found) or FALSE if the value was found. If the first VLOOKUP returns a #N/A error, the "Value Not Found" message will be returned. If the ISNA function returns FALSE, then the 2nd VLOOKUP will be evaluated.

So, in your situation, your formula (using Named Ranges) might look like this. (Make sure that you read the Notes at the bottom of this post which offer a much shorter formula if you are 100% sure that the ItemCode will be found if the MasterCode isn't. The formula shown here will cover all bases: MasterCode not found but ItemCode found and also neither MasterCode or ItemCode found. You may not need all of this, but it's a good lesson anyway.)

=IF(NOT(ISNA(VLOOKUP(Mastercode, Database1, 3, 0))), VLOOKUP(Mastercode, Database1, 3, 0),IF(NOT(ISNA(VLOOKUP(ItemCode, Database2, 2, 0))),VLOOKUP(ItemCode, Database2, 2, 0),"Neither Value Found"))

How this should work is as follows:

If the 1st VLOOKUP does not return a #N/A error, then the MasterCode was found and the VLOOKUP used as the

value_if_trueargument of the IF function will be evaluated. In other words, the MasterCode VLOOKUP will return the desired value.If the 1st VLOOKUP

doesreturn a #N/A error, then the MasterCode was not found and thevalue_if_falseargument of the IF function will be evaluated. Since that argument contains a NOT(ISNA(VLOOKUP..)) for the ItemCode, it will first check to see if the ItemCode was found and evaluate the ItemCode VLOOKUP if it was, or return the message that neither of the values were found.You will note that I used Database1 and a

col_index_numof 2 for the MasterCode VLOOKUPs and Database2 and acol_index_numof 3 for the ItemCode VLOOKUPs since we have to adjust thetable_arrayandcol_index_numarguments since we are searching in a different column.2 things to consider:

1 - If you are 100% sure that the ItemCode will be found if the MasterCode isn't, then you don't have to check the ItemCode VLOOKUP with the ISNA function or include the "Not found" message. That will result in a considerably shorter formula. I was just trying to cover all bases.

2 - The formula can also be shortened by eliminating the NOT functions and rearranging the various arguments in the formula. I used the NOT's so that you could follow the formula from left to right a little easier.

Putting those 2 notes together, you might get away with this:

=IF(ISNA(VLOOKUP(Mastercode, Database1, 3, 0)),VLOOKUP(ItemCode, Database2, 2, 0),VLOOKUP(Mastercode, Database1, 3, 0))

I hope that all makes sense! Come on back if not.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you DerbyDad03! That's the best answer I have ever recieved. Very detailed and it works!!!!

Ask Your Question

Weekly Poll