Combining the lookup of two worksheets into a single cell?

monkey_kebab

New Member
Joined
Apr 21, 2006
Messages
10
Howdy -

I have a report which pulls data from multiple worksheets (all contained within the same spreadsheet file). Because the raw data comes from multiple databases we use the following formulas to verify the individuals from one source are included in the results from the other:

=VLOOKUP(C2,’C’!A:B,2,FALSE)
=VLOOKUP(C2,’D’!A:B,2,FALSE)

One formula looks up the individual’s unique ID in column ‘C’ and compares it against a list in column ‘A’ of worksheet ‘C’. If it finds a match it displays the corresponding text in column ‘B’ of worksheet ‘C’ (the word ‘Reconciled’)… ‘#N/A’ is displayed if no match is found.

The second formula does the same thing except it runs in the next column over & looks through a list on worksheet ‘D’.

We are forced to split the data from one source between worksheets 'C' & 'D' because it comprises more than 65,000 rows of information.

What I’m wondering is it’s possible to modify the formula so we can have it do the lookup of both worksheets from one cell. Right now we have two columns of results, with ‘Reconciled’ and ‘#N/A’ displayed in one or the other for each individual. We want to be able to identify those who do not show up in either list (right now they show up with ‘#N/A’ results in both of these columns).

Thanks,
Bob T.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
Hi Bob

Please try

=IF(AND(ISNA(VLOOKUP(C2,'C'!A:B,2,FALSE) ),ISNA(VLOOKUP(C2,D!A:B,2,FALSE))),"NOT OK", "OK")

HTH
PGC
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
Hi again

I'm glad it works.

However it's not completely correct. We are using vlookup that gives the result in the column next to the item found. And yet the formula just tells if the item was found or not.

So, 2 alternatives.

First alternative. You just want to know if the item is found. In this case you don't need VLOOKUP.

=IF(AND(ISNA(MATCH(C2,'C'!A:A,FALSE)),ISNA(MATCH(C2,D!A:A,FALSE))),"NOT OK", "OK")

Second alternative. You not only want to find the item, but also display the value in the column next to it. In this case:

=IF(NOT(ISNA(VLOOKUP(C2,'C'!A:B,2,FALSE))),VLOOKUP(C2,'C'!A:B,2,FALSE),IF(NOT(ISNA(VLOOKUP(C2,D!A:B,2,FALSE))),VLOOKUP(C2,D!A:B,2,FALSE),"Not found"))

Hope it's better now.
PGC
 

Forum statistics

Threads
1,176,077
Messages
5,901,269
Members
434,884
Latest member
ares0027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top