# Combining the lookup of two worksheets into a single cell?

#### monkey_kebab

##### New Member
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi Bob

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

HTH
PGC

Hi PGC -

That works great!!! You've just made my world a bit easier!

Thanks!
Bob T.

Hi again

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:

Hope it's better now.
PGC

Replies
4
Views
131
Replies
5
Views
290
Replies
3
Views
332
Replies
8
Views
127
Replies
4
Views
374

1,219,811
Messages
6,150,366
Members
450,955
Latest member
rose8693

### 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.

### Which adblocker are you using?

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

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