Excel - INDEX Function Problem - Expert Solution

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.

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.