Two-Dimensional Match function

[puneet1011]

I've my data in the following format:
Category Code 1 Code 2 Code 3
A AB121 AN131
B AB102 AB103 AN321
A AD211

What I want to do is basically return the "Category" based on the "Code". If the match() worked for 2-d arrays, this would be pretty simple but unfortunately it doesn't. Any suggestions?

[zbor]

Please upload example workbook.

Write desired output and where you want it.

[puneet1011]

Hi,
Please find attached the sample file.

[puneet1011]

I'm stuck with the same problem again. Any solutions?

[Czeslaw]

Can such a solution using AGGREGATE function will do.

=INDEX($A$17:$A$25,AGGREGATE(15,6,ROW(A$1:A$15)/($B$17:$E$25=A2),1))

[samba_ravi]

or try below formula

=IFERROR(INDEX($A:$A,MIN(INDEX(($B$17:$E$25<>$A2)*10^10+ROW($B$17:$E$25),0))),"")

[zbor]

As long as your category is simple A, B, C..., Z then you can use CHAR.

[samba_ravi]

b2=INDEX($A:$A,SUMPRODUCT(($B$17:$E$25=$A2)*ROW($B$17:$E$25)))
try this and copy towards down