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!
