Excel - How to Use a VLOOKUP Formula - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Vlookup or match help, having issues creating the formula
Solved by X. Y. in 18 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 14/11/2017 - 06:29
Hi, Welcome to Got It Pro.
Excelchat Expert 14/11/2017 - 06:29
I am observing the sample in the preview
User 14/11/2017 - 06:30
OKay
User 14/11/2017 - 06:30
So here we go
User 14/11/2017 - 06:30
I want a formula
User 14/11/2017 - 06:30
to search Column E and Column C to find who matches
User 14/11/2017 - 06:30
sorry
User 14/11/2017 - 06:30
they are not in orders
User 14/11/2017 - 06:30
and they wont be in order
User 14/11/2017 - 06:31
so check column E
User 14/11/2017 - 06:31
check column C
User 14/11/2017 - 06:31
find their match
Excelchat Expert 14/11/2017 - 06:31
As per the given data, 222 is matching in both C and E columns
User 14/11/2017 - 06:31
and then compare their answer in column A and in column D
User 14/11/2017 - 06:31
and tel me who matches
User 14/11/2017 - 06:32
So from columns A through E
User 14/11/2017 - 06:32
I want to get columns F and G
User 14/11/2017 - 06:32
questions?
Excelchat Expert 14/11/2017 - 06:32
Can you please explanation of G2 cell
User 14/11/2017 - 06:33
Look at F column
User 14/11/2017 - 06:33
999
User 14/11/2017 - 06:33
look at E and C
User 14/11/2017 - 06:33
thats 999
Excelchat Expert 14/11/2017 - 06:33
*Please provide the explanation for G2 cell.
User 14/11/2017 - 06:33
im gtting there sir
Excelchat Expert 14/11/2017 - 06:33
Sure
User 14/11/2017 - 06:33
999 of C4 gives 0 of P_R_0
Excelchat Expert 14/11/2017 - 06:33
C and E having 999. But, they are in different rows
User 14/11/2017 - 06:34
999 in E2
User 14/11/2017 - 06:34
gives 0 in P_R_1
User 14/11/2017 - 06:34
0 = 0
User 14/11/2017 - 06:34
so G2 = 1
Excelchat Expert 14/11/2017 - 06:34
Ok. I understood the requirement. I am working on it.
User 14/11/2017 - 06:34
IF they did not match (0=1)
User 14/11/2017 - 06:35
so G2 = 0
Excelchat Expert 14/11/2017 - 06:35
Thanks for the explanation.
Excelchat Expert 14/11/2017 - 06:36
=if(index(D:D, match(F2,E:E,0),1)=index(A:A, match(F2,C:C,0),1),1,0)
Excelchat Expert 14/11/2017 - 06:37
I am explaining the formula
Excelchat Expert 14/11/2017 - 06:37
I am first matching the rows using match function and then getting the corresponding 1,0 using index function.
Excelchat Expert 14/11/2017 - 06:38
When, the 2 values are matching, the result is 1. Otherwise, it is 0
Excelchat Expert 14/11/2017 - 06:40
So, the formula works well as per the problem description.
User 14/11/2017 - 06:41
im getting an error
User 14/11/2017 - 06:41
so this is dummie data
User 14/11/2017 - 06:41
I need it for a confidential document
User 14/11/2017 - 06:41
and when I copy pasted the formular
User 14/11/2017 - 06:41
its not running
Excelchat Expert 14/11/2017 - 06:42
Whether the column location are correct. I have written formula in column H. I can shift the formula to column G
Excelchat Expert 14/11/2017 - 06:42
Now, Please try copying the formula.
User 14/11/2017 - 06:42
So to the right is a better representatin
User 14/11/2017 - 06:43
Do I need column F?
User 14/11/2017 - 06:43
Or does ur formula G work without column F
User 14/11/2017 - 06:43
and does ur formula work with row 1 being inserted
Excelchat Expert 14/11/2017 - 06:43
If you does not need that, i can change the formula
Excelchat Expert 14/11/2017 - 06:44
Yes. It works with one row insertion
User 14/11/2017 - 06:44
I do need F
User 14/11/2017 - 06:44
Because F will tell me what G represents
User 14/11/2017 - 06:44
So G3 answer is specific for F3
Excelchat Expert 14/11/2017 - 06:45
Ok. So, in that case, the formula should work.
User 14/11/2017 - 06:45
it worked
Excelchat Expert 14/11/2017 - 06:45
Yes
User 14/11/2017 - 06:45
so 1 = match
User 14/11/2017 - 06:45
0 = no match
User 14/11/2017 - 06:45
correcT?
Excelchat Expert 14/11/2017 - 06:45
Yes. 1=match
Excelchat Expert 14/11/2017 - 06:46
and 0 is not match.
Excelchat Expert 14/11/2017 - 06:46
It works for any number of rows. As i am fixing the columns
User 14/11/2017 - 06:46
Its perfect
User 14/11/2017 - 06:46
you are amazing
User 14/11/2017 - 06:46
thank you for your work
Excelchat Expert 14/11/2017 - 06:46
Thank you.
User 14/11/2017 - 06:46
Have a nice day
Excelchat Expert 14/11/2017 - 06:47
You are welcome:) Please visit Got It Pro
Excelchat Expert 14/11/2017 - 06:47
Have a great day ahead

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc