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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cat 1[/TD]
[TD]1000[/TD]
[TD]1200[/TD]
[TD]1450[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Cat 2[/TD]
[TD]1002[/TD]
[TD]1305[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cat 3[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Cat 4[/TD]
[TD]1503[/TD]
[TD]1832[/TD]
[TD]1900[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Cat 5[/TD]
[TD]1235[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
Cat 1
Cat 2	Cat 4
Cat 3
Cat 4
Cat 5

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]1450[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1002[/TD]
[TD="align: right"]1305[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1832[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1503[/TD]
[TD="align: right"]1832[/TD]
[TD="align: right"]1900[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1235[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=INDEX(A1:A5,SUMPRODUCT((B1:D5=F2)*(ROW(B1:B5)))-ROW(B1:B5)+1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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