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.
