Solved Reverse lookup? (1 Viewer)

[alcwho]

Hi, I have the following table for value lookup.

Actually, a user has 2 criteria, i) what Big group he is in; ii) what sub group he is in under the Big group. Then based on these 2 criteria, lookup what fruit he needs.

For example, User is in i) B-2 Big group; and ii) be-2 subgroup, then he needs Banana.

I don't know how to build the excel formula to get the result. It seems not using Index & Match function under this case. So I stuck in here.

Can anyone help me to solve this?

Thanks in advance

	Apple	Apple	Apple	Banana	Banana	Banana	Orange	Orange	Orange
A-1	aa-1	ab-1	ac-1	ad-1	ae-1	af-1	ag-1	ah-1	ai-1
B-2	ba-2	bb-2	bc-2	bd-2	be-2	bf-2	bg-2	bh-2	bi-2
C-3	ca-3	cb-3	cc-3	cd-3	ce-3	cf-3	cg-3	ch-3	ci-3
D-4	da-4	db-4	dc-4	dd-4	de-4	df-4	dg-4	dh-4	di-4
E-5	ea-5	eb-5	ec-5	ed-5	ee-5	ef-5	eg-5	eh-5	ei-5

 

[June7]

Need the user parameters somewhere perhaps on same sheet:

I	M	N	O
User	Big Group	Subgroup	Fruit
1	B-2	be-2	Banana
2	D-4	dg-4	Orange
3	E-5	ea-5	Apple

Then formula in column O
=INDEX($B$1:$J$1,MATCH(N2,INDEX($B$2:$J$6,MATCH(M2,$A$2:$A$6,0),0),0))

 

[alcwho]

Need the user parameters somewhere perhaps on same sheet:

I	M	N	O
User	Big Group	Subgroup	Fruit
1	B-2	be-2	Banana
2	D-4	dg-4	Orange
3	E-5	ea-5	Apple

Then formula in column O
=INDEX($A$1:$J$1,MATCH(N2,INDEX($A$2:$J$6,MATCH(M2,A$2:$A$6,0),0),0))

Thanks for your help. Let me take a look

 

[June7]

Since subgroup is unique in example, BigGroup value is not needed to identify column.
=INDEX($B$1:$J$1,SUMPRODUCT(MAX(($B$2:$J$6=N2)*(COLUMN($B$2:$J$6))))-COLUMN($B$2)+1)

Or this version which is an array formula.
{=INDEX($B$1:$J$1,MAX(IF($B$2:$J$6=N2,COLUMN($B$2:$J$6)))-COLUMN($B$2)+1)}

 

[alcwho]

Thanks all