Solved Vlookup across multiple worksheets

May 21, 2013 at 12:48:44
Specs: Windows 7
I have 2 different reports that have part #'s along with brand names, just in different columns.
The first report has the part # in column A and brand in column D.
The second has part # in column C with brand in column B.
There is a 3rd sheet which acts as the main worksheet, whereas the reports are simply there to have data pulled from.
The 3rd sheet has a list of all part #'s (from each report and some that are on both.)
I need a vlookup (or other applicable function) that will pull in the name brand from either report.

I hope I explained that correctly.

See More: Vlookup across multiple worksheets

May 21, 2013 at 15:37:35
✔ Best Answer
I am assuming two things:

1 - You only need to pull the brand from one report or the other, even if the part number appears in both reports.

2 - Every part number on Sheet3 can be found on either Sheet1 or Sheet2 or both.

If those assumptions are correct, then this should work for you:


Here's what's going on...

The IF(ISNA(VLOOKUP...)) tries to VLOOKUP the value in A1 in Sheet1!A1:D7. If that VLOOKUP returns a #N/A error, meaning that the value in A1 was not found, the INDEX-MATCH section looks up A1 in Column C of Sheet2 and returns the value from Column B. (We have to use INDEX-MATCH because we are looking left for the "brand" and VLOOKUP can only look right.) However, if the IF(ISNA(VLOOKUP...) is FALSE, meaning that the value in A1 was found on Sheet1, then the value_if_false argument of the IF statement does a VLOOKUP to return the "brand" from Sheet1.

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

Report •

May 22, 2013 at 05:45:37
Thank you! That worked perfectly.

Report •
Related Solutions

Ask Question