Solved Searching with Vlookup

September 3, 2011 at 02:13:46
Specs: Windows XP
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.

See More: Searching with Vlookup

Report •

September 3, 2011 at 08:16:37
✔ Best Answer
First, a posting tip: Before posting any more data in this forum, please click on the blue line at 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_value in the first column of the table_array. Since the ItemCode is not in the same column as the MasterCode, you can't use the same reference for the 2 table_arrays. You'll have to use a different table_array reference and therefore a different col_index_number for 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_value is found, and then either returns the value from the col_index_num specified 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_true argument of the IF function will be evaluated. In other words, the MasterCode VLOOKUP will return the desired value.

If the 1st VLOOKUP does return a #N/A error, then the MasterCode was not found and the value_if_false argument 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_num of 2 for the MasterCode VLOOKUPs and Database2 and a col_index_num of 3 for the ItemCode VLOOKUPs since we have to adjust the table_array and col_index_num arguments 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.

Report •

September 8, 2011 at 23:16:55
Thank you DerbyDad03! That's the best answer I have ever recieved. Very detailed and it works!!!!

Report •

Related Solutions

Ask Question