Index Match to find a Value and return first row

[roundaboutrc]

Hello everyone,

Not sure if this is possible, but I think to think "All Things are Possible", so I figured I would ask. I have some data where the first column is category then every column after that is id's for that category.

	A	B	C	D
1	Cat 1	1000	1200	1450
2	Cat 2	1002	1305
3	Cat 3	2000
4	Cat 4	1503	1832	1900
5	Cat 5	1235

<tbody>
</tbody>

I am looking for a formula to lookup an ID say in this case 1832 and return Cat 4, where the ID could be located anywhere from B1:D5. I have tried Index and Match [=Index(A1:A5,Match(1832,B1:D5,0))], but it doesn't like the B1:D5 it appears Match can only look at a single row or column.

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G
1	Cat 1	1000	1200	1450
2	Cat 2	1002	1305			1832	Cat 4
3	Cat 3	2000
4	Cat 4	1503	1832	1900
5	Cat 5	1235
Sheet2

Cell Formulas
Range	Formula
G2	=INDEX(A1:A5,SUMPRODUCT((B1:D5=F2)*(ROW(B1:B5)))-ROW(B1:B5)+1)

 

[roundaboutrc]

Not exactly sure why, but hopefully you can help. This works on the sample, but when I use it against the real data set of 7200 product IDs and 69 categories, the first 69 results is just a replication of categories.

Also I am getting some #Value!e rrors? How will this handle Product IDs that aren't found in the data set?N/A? Say 999 in the table below.

How about

	A	B	C	D	E	F	G
1	Cat 1	1000	1200	1450
2	Cat 2	1002	1305			1832	Cat 4
3	Cat 3	2000
4	Cat 4	1503	1832	1900
5	Cat 5	1235

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula G2 =INDEX(A1:A5,SUMPRODUCT((B1:D5=F2)*(ROW(B1:B5)))-ROW(B1:B5)+1) <tbody> </tbody>

<tbody>
</tbody>

 

[Fluff]

Why you are getting errors I cannot say, but check that none of the data you are looking at contains errors.
However that formula will fail if you have duplicate IDs.
Try
=INDEX(A1:A5,SMALL(IF(B1:D5=F2,ROW(B1:D5)-ROW(B1)+1),1))

This is an array formula and needs to be confirmed with Ctrl Shift Enter