Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula (maybe Index and Match) to find a value in two different row
Solved by E. C. in 53 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
23/03/2018 - 12:47
Hey good day!
User
23/03/2018 - 12:47
Hello
Excelchat Expert
23/03/2018 - 12:47
Looks like you need to find a data from an array?
User
23/03/2018 - 12:48
correct
User
23/03/2018 - 12:48
I'll write an example in the spreadsheet first
Excelchat Expert
23/03/2018 - 12:48
Okay cool
User
23/03/2018 - 12:50
So i would like to find out the data (Data 1 & Data 2) based on Data 3
User
23/03/2018 - 12:51
for example data 3 = 4, data 1 = ?, data 2 = ?
Excelchat Expert
23/03/2018 - 12:51
Okay, you need the result in one cell or separate cells?
User
23/03/2018 - 12:51
but the catch is, i want it to be the first two values
User
23/03/2018 - 12:51
just in one cell
User
23/03/2018 - 12:52
Do you get my question?
User
23/03/2018 - 12:53
so i think it should be two different formula..maybe
Excelchat Expert
23/03/2018 - 12:53
So we want to get BCC and GDD?
User
23/03/2018 - 12:53
correct
User
23/03/2018 - 12:54
yes
User
23/03/2018 - 12:55
is it possible to do that?
User
23/03/2018 - 12:55
i have tried index and match but it will only return the first answer
Excelchat Expert
23/03/2018 - 12:56
I think using vlookup will work but you have to use 2 vlookups
Excelchat Expert
23/03/2018 - 12:57
the tricky part is to make it work for the first 2 variables only. It's going to be long but we can add multiple ifs
User
23/03/2018 - 12:58
okay
Excelchat Expert
23/03/2018 - 01:01
I used your formulas and just concatenated
Excelchat Expert
23/03/2018 - 01:02
Where do you want the results to show, in one column?
User
23/03/2018 - 01:03
just in two cells
User
23/03/2018 - 01:03
1st cell is for first result
Excelchat Expert
23/03/2018 - 01:10
I think the getting it to return only the first 2 might not be possible.
User
23/03/2018 - 01:11
but to get the first value of 4 is possible right
User
23/03/2018 - 01:11
i saw the formula that you apply return 1
Excelchat Expert
23/03/2018 - 01:12
Let's try getting it to return the first value of 4. Hang on.
User
23/03/2018 - 01:13
if that is possible that will be good enough
Excelchat Expert
23/03/2018 - 01:14
Try manipulating the data to confirm the formula
User
23/03/2018 - 01:15
i mean i saw you try to return the result to 0 and 1 just now
Excelchat Expert
23/03/2018 - 01:15
Yeah, I was going to use that to get us to return the first 2 values of 4
Excelchat Expert
23/03/2018 - 01:15
But the logic won't work
User
23/03/2018 - 01:16
can we get at least to return the value in the blue cell
Excelchat Expert
23/03/2018 - 01:16
You want to get the value in the same row?
Excelchat Expert
23/03/2018 - 01:17
Okay hang on.
User
23/03/2018 - 01:17
the 0 and 1 logic you apply just now i think it will return 1 to the blue cell
Excelchat Expert
23/03/2018 - 01:18
Yes, the logic was to get 1 for the first 2 values and get it to index only if the value is 1
Excelchat Expert
23/03/2018 - 01:34
This is the only issue, we can't make it to work only on the first 2 values of 4
User
23/03/2018 - 01:35
ok i think this should work
Excelchat Expert
23/03/2018 - 01:35
Okay cool.
User
23/03/2018 - 01:38
thanks for your help
Excelchat Expert
23/03/2018 - 01:38
Anytime. Have a good one! :)
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.
