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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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,884
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,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

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