Find Value in a Chart and Return Column Description

[dwonsowski]

Hi, I can't find what I was looking for and was hoping someone could easily figure this out. I have a 2 worksheets, one has a master list of the part numbers and the other a chart to list the similar parts (All models have similar parts a, b, c & d, but the part numbers are different across all models). On the master parts list, I would like make a formula to read what part number cell is next to it and return the values of the chart. Ideally, it would return a value for part number 09 that states b-Model C and part number 10 is b-Model D. I tried the vlookup and some of the cells started reading from a row above or that it would only look for the part number in the first column. I couldn't get the index/match to work correctly either. Thanks in advance!

	Model A	Model B	Model C	Model D	Model E	Model F
a	01	02	03	04	05	06
b	n/a	08	09	10	11	12
c	13	14	15	16	17	18
d	19	20	21	n/a	23	24

<tbody>
</tbody>

 

[Domenic]

Assuming that A1:G5 contains the data, and that each part number only occurs once within the data, try the following...

Let I2 contain 09
Let I3 contain 10

J2, confirmed with CONTROL+SHIFT+ENTER, and copy down:

=INDEX($A$2:$A$5,SMALL(IF($B$2:$G$5=I2,ROW($B$2:$G$5)-ROW($B$2)+1),1))&"-"& INDEX($B$1:$G$1,SMALL(IF($B$2:$G$5=I2,COLUMN($B$1:$G$1)-COLUMN($B$1)+1),1))

Hope this helps!

 

[dwonsowski]

Yes! That is it! Thank you!