Excel - INDEX MATCH Function - Expert Solution

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

Hello, I am working with two workbooks. I am trying to get specific information from one onto the other using 3 criteria. I currently using a index match formula. Both sheets have the same information but one has a response that I am trying to match and populate onto the new report. However, instead of looking up the actual value of the cell - its matching the location and giving me whats in that cell. =INDEX('[Arrow OOR - 9-11-18.xlsx]Response'!$A:$L,MATCH(1,(A:A=A4)*(B:B=B4)*(F:F=F4),0),12)
Solved by I. C. in 52 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 18/09/2018 - 07:25
1-5
Excelchat Expert 18/09/2018 - 07:25
HI, pls could you describe topic.
User 18/09/2018 - 07:26
essentially I am trying to use vlookup but I have 3 criteria
User 18/09/2018 - 07:26
I used a index match
User 18/09/2018 - 07:27
but its not looking up the values just the location of the cells
Excelchat Expert 18/09/2018 - 07:27
OK, pls more easy will be when I will see your table and formulas
Excelchat Expert 18/09/2018 - 07:27
could you share it with me
User 18/09/2018 - 07:28
its from work so I am not sure that i can
User 18/09/2018 - 07:29
I can create like an example though so you can get an idea as to what I am trying to do
Excelchat Expert 18/09/2018 - 07:29
OK, I understand, so if I understand it correctly you want to use your formula and instead of results you see only formula?
Excelchat Expert 18/09/2018 - 07:29
This will be great
User 18/09/2018 - 07:30
I have two sheets
Excelchat Expert 18/09/2018 - 07:30
ok
User 18/09/2018 - 07:30
they have the same information --- rather two books
Excelchat Expert 18/09/2018 - 07:30
ok
User 18/09/2018 - 07:32
The only difference being that the comments on one has been filed out by our customer
Excelchat Expert 18/09/2018 - 07:33
ok
User 18/09/2018 - 07:33
I am trying to get those comments from the old report on the new report so I know which ones I have to follow up on
Excelchat Expert 18/09/2018 - 07:33
OK so do you want to combine this two tables and put together this two comment?
User 18/09/2018 - 07:34
The formula does give me a value --- it just giving me the value of the location and the actually looking up the value if that makes sense
Excelchat Expert 18/09/2018 - 07:34
which parameter is unique for both tables (books)
Excelchat Expert 18/09/2018 - 07:35
is it po #
User 18/09/2018 - 07:35
I need to use three columns to make sure I get the right due date po and part number
Excelchat Expert 18/09/2018 - 07:37
thanks for your nice example, I need some time to analyse it..
User 18/09/2018 - 07:39
there will and are multiple due dates for the same po and part number but the combination of the three is what makes the comment unique and thats what I need the comment for that delivery date, po and part number
Excelchat Expert 18/09/2018 - 07:40
where do you want to put your formula?
User 18/09/2018 - 07:40
in the empty comments column
User 18/09/2018 - 07:40
I want to compare the new report (no comments) with the old (with comments)
Excelchat Expert 18/09/2018 - 07:41
ok...
User 18/09/2018 - 07:42
The data isn't identical like this example but I am in essence trying to find the ones that don't have comments and follow up on those and even depending on the comment follow up on those also
Excelchat Expert 18/09/2018 - 07:43
I understand it, it is OK for me. So can we say that as first you want have comment from Sheet2 in Sheet1 based on ???
Excelchat Expert 18/09/2018 - 07:44
Part num or this delivery date, po and part number
Excelchat Expert 18/09/2018 - 07:45
I think as you mentioned delivery date, po and part number should be correct..
User 18/09/2018 - 07:47
yes the three criteria are due date, po # and part number
User 18/09/2018 - 07:48
I currently have an index match formula but its not giving me the the right comment rather just matching the location and not the values of what the cells holds
Excelchat Expert 18/09/2018 - 07:48
ok, are this three criterie together unique?
User 18/09/2018 - 07:48
yes they are thats why I picked that combination.
Excelchat Expert 18/09/2018 - 07:49
Good so I think we are closer to solution, pls give me few minutes to prepare some proposal
User 18/09/2018 - 07:56
Ok I see what you did
User 18/09/2018 - 07:56
was trying to see if there was a way to avoid creating an extra column
Excelchat Expert 18/09/2018 - 07:56
So pls is it what do you need?
Excelchat Expert 18/09/2018 - 07:57
ok
Excelchat Expert 18/09/2018 - 07:57
give me second
User 18/09/2018 - 07:57
i am trying to duplicate the formula I have so you can see what I mean also
Excelchat Expert 18/09/2018 - 07:58
I don´t think that without special column it will work
Excelchat Expert 18/09/2018 - 07:59
ok, pls show me your formula again
User 18/09/2018 - 08:01
ok it looked like it worked but the information is in the same location as each other
Excelchat Expert 18/09/2018 - 08:02
I briefly checked your formula and I don´t think it will work as you need
Excelchat Expert 18/09/2018 - 08:02
can we now focus to my proposal?
Excelchat Expert 18/09/2018 - 08:03
solution?
User 18/09/2018 - 08:03
aaahhh I was afraid of that
User 18/09/2018 - 08:03
alright then I will do it with a combined column
Excelchat Expert 18/09/2018 - 08:04
give me second
Excelchat Expert 18/09/2018 - 08:05
index function in formula works different way, so my experience and easier soulution is with this special "Index" column
User 18/09/2018 - 08:07
ok makes sense.
Excelchat Expert 18/09/2018 - 08:07
So idea is to as first create unique index for both tables. This is due date, po # and part number. In example table could looks as =A6&"_"&B6&"_"&D6
Excelchat Expert 18/09/2018 - 08:08
It is up to you how you define your index.
User 18/09/2018 - 08:08
yes I am familiar with just was trying to figure out if there was a different way
User 18/09/2018 - 08:08
thank you for your help
User 18/09/2018 - 08:09
I will just create an extra column with the combined criteria
Excelchat Expert 18/09/2018 - 08:09
You are welcome, so is your question answered?
User 18/09/2018 - 08:09
yes it is.
Excelchat Expert 18/09/2018 - 08:10
Great, it was pleasure help you. Pls give feedback and I wish you success.
User 18/09/2018 - 08:10
thanks. have a great day
Excelchat Expert 18/09/2018 - 08:11
Pls could you press "Answered" button , I hope you see it
User 18/09/2018 - 08:11
where might it be?
Excelchat Expert 18/09/2018 - 08:11
:-)
Excelchat Expert 18/09/2018 - 08:12
OK, I dont know, so should be some button to finish session
User 18/09/2018 - 08:13
i dont see it
Excelchat Expert 18/09/2018 - 08:14
oh no, I see button "Done" but when I press it I received some failure message. Do you see "Done" button
Excelchat Expert 18/09/2018 - 08:14
right up
User 18/09/2018 - 08:15
no I just see a countdown
Excelchat Expert 18/09/2018 - 08:15
ok, so I will close it from my side, have you nice day and thanks
User 18/09/2018 - 08:17
thanks u too

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.