**Question description:**

*This user has given permission to use the problem statement for this blog.*

I have a main dataset of about 500,000 line items. I want to lookup the items from 3 different datasets. Each of these datasets has got about 200,000 line items. I want to use the if and vlookup function to return a " Yes" or "No" answer.

Solved by O. Q. in 40 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
07/09/2018 - 03:41

Hello

Excelchat Expert
07/09/2018 - 03:41

Welcome

User
07/09/2018 - 03:42

I have a big excel query?

Excelchat Expert
07/09/2018 - 03:42

Okay... I'm listening.

User
07/09/2018 - 03:42

Looking for a if and vlookup formular

User
07/09/2018 - 03:43

I have a main dataset of about 500,000 line items. I want to lookup the items from 3 different datasets. Each of these datasets has got about 200,000 line items. I want to use the if and vlookup function to return a " Yes" or "No" answer.

Excelchat Expert
07/09/2018 - 03:43

Alright, I need to see some sample data.

Excelchat Expert
07/09/2018 - 03:44

Can you please share the file or write on the preview window?

Excelchat Expert
07/09/2018 - 03:47

Please let me know when you are finished.

User
07/09/2018 - 03:49

I want to lookup patient ID from the main sheet in sheet1 and sheet2?

Excelchat Expert
07/09/2018 - 03:50

So, if the patient ID of sheet1 is present in Main sheet, you need to see "Yes", otherwise "No". Same goes for Sheet2, right?

User
07/09/2018 - 03:50

Return Yes or No in the Patient ID exists column

Excelchat Expert
07/09/2018 - 03:51

Oh I see.

Excelchat Expert
07/09/2018 - 03:51

Thank you for making it clear.

Excelchat Expert
07/09/2018 - 03:51

Please give me couple of minutes.

User
07/09/2018 - 03:56

Are you about to get it?

Excelchat Expert
07/09/2018 - 03:56

Yeah, I'm almost done.

Excelchat Expert
07/09/2018 - 03:57

Please stay with me. And don't worry about the time, we can extend it if needed.

Excelchat Expert
07/09/2018 - 03:59

=IF(OR(ISNUMBER(MATCH(A2,' sheet 1'!A:A,0)),ISNUMBER(MATCH(A2,'sheet 2'!A:A,0))),"Yes","No")

Excelchat Expert
07/09/2018 - 03:59

We can use this formula.

Excelchat Expert
07/09/2018 - 04:00

MATCH(A2,' sheet 1'!A:A,0) checks whether the patient ID is present in sheet1

Excelchat Expert
07/09/2018 - 04:00

And MATCH(A2,'sheet 2'!A:A,0) does the same but in sheet2.

Excelchat Expert
07/09/2018 - 04:01

If a patiend ID is found on either of these two sheets, Match formula will return a number.

Excelchat Expert
07/09/2018 - 04:01

So using ISNUMBER function we are checking whether The patient is present in those two sheets or not.

User
07/09/2018 - 04:01

Let me try it

Excelchat Expert
07/09/2018 - 04:02

Sure, please take your time. Let me know if you need any clarification.

User
07/09/2018 - 04:03

What if I have a third similar sheet

User
07/09/2018 - 04:03

What formular to use

Excelchat Expert
07/09/2018 - 04:04

Add a similar ISNUMBER(MATCH()) formula inside the OR like this

User
07/09/2018 - 04:04

That is 3 0r 4 work sheets to look up the data

User
07/09/2018 - 04:04

give me an exampla

Excelchat Expert
07/09/2018 - 04:04

=IF(OR(ISNUMBER(MATCH(A2,' sheet 1'!A:A,0)),ISNUMBER(MATCH(A2,'sheet 2'!A:A,0)),ISNUMBER(MATCH(A2,' sheet 3'!A:A,0))),"Yes","No")

Excelchat Expert
07/09/2018 - 04:05

This is the example for 3 sheet.

Excelchat Expert
07/09/2018 - 04:06

Makes sense?

Excelchat Expert
07/09/2018 - 04:09

If it doesn't still solve the problem please let me know.

User
07/09/2018 - 04:10

it says too many arguments entered

User
07/09/2018 - 04:10

the sheets 1, 2 & 3 are in different work books

Excelchat Expert
07/09/2018 - 04:11

The formula should be the same except you have to enter the workbook names before cell reference.

Excelchat Expert
07/09/2018 - 04:12

For example:

User
07/09/2018 - 04:12

too many arguments entered for this function is the message I get

Excelchat Expert
07/09/2018 - 04:12

Let's say workbook1, workbook2 and workbook3 are the names.

Excelchat Expert
07/09/2018 - 04:13

Okay, I'm giving the modified formula example...

Excelchat Expert
07/09/2018 - 04:14

=IF(OR(ISNUMBER(MATCH(A2,[workbook1.xlsx]' sheet 1'!A:A,0)),ISNUMBER(MATCH(A2,[workbook2.xlsx]'sheet 2'!A:A,0)),ISNUMBER(MATCH(A2,[workbook3.xlsx]' sheet 3'!A:A,0))),"Yes","No")

User
07/09/2018 - 04:14

It works thanks

Excelchat Expert
07/09/2018 - 04:15

Its great to hear. :)

Excelchat Expert
07/09/2018 - 04:15

You are a brilliant learner.

Excelchat Expert
07/09/2018 - 04:15

Please let me know if you have any question.

Excelchat Expert
07/09/2018 - 04:17

Please do leave a 5 star rating for me when the chat ends. I would mean a lot to me.

Excelchat Expert
07/09/2018 - 04:19

The chat will end soon unless you extend again.

Excelchat Expert
07/09/2018 - 04:21

Thank you, have a great day ahead!