Index & match

[raymondboze]

Trying to do an Index and Match looking for one particular criteria in multiple ranges of cells and display the value of the matched cell elsewhere in a workbook. For example: I want the value in the second column where it is matched to the value of the cell in the third column to display on a second worksheet.[TABLE="width: 500"]
<tbody>[TR]
[TD]T456[/TD]
[TD]6[/TD]
[TD]A31[/TD]
[TD]T485[/TD]
[TD]4[/TD]
[TD]A11[/TD]
[/TR]
[TR]
[TD]T456[/TD]
[TD]4[/TD]
[TD]A21[/TD]
[TD]T485[/TD]
[TD]3[/TD]
[TD]A41[/TD]
[/TR]
[TR]
[TD]T456[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]T485[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have figured out that part but what I can't figure out is how to get it to do the same thing if say for example the "A31" in the third column was where the "A41" is in the sixth column. Basically I need it to do this for a total of 9 columns and wherever it finds "A31" at i need the value in the cell to the left of it to display elsewhere. Can anyone help me with this?

 

[Domenic]

Try the following...


A2:J10


[TABLE="width: 640"]
<tbody>[TR]
[TD="class: xl63, width: 64"]T456[/TD]
[TD="class: xl63, width: 64, align: right"]100[/TD]
[TD="class: xl63, width: 64"]A31[/TD]
[TD="class: xl63, width: 64"]T485[/TD]
[TD="class: xl63, width: 64, align: right"]4[/TD]
[TD="class: xl63, width: 64"]A11[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]A31[/TD]
[TD="class: xl63, width: 64, align: right"]5[/TD]
[TD="class: xl63, width: 64, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl63"]T456[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"]A21[/TD]
[TD="class: xl63"]T485[/TD]
[TD="class: xl63, align: right"]400[/TD]
[TD="class: xl63"]A31[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]400[/TD]
[/TR]
[TR]
[TD="class: xl63"]T456[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]T485[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]200[/TD]
[/TR]
[TR]
[TD="class: xl63"]T456[/TD]
[TD="class: xl63, align: right"]200[/TD]
[TD="class: xl63"]A31[/TD]
[TD="class: xl63"]T485[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"]A11[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]300[/TD]
[/TR]
[TR]
[TD="class: xl63"]T456[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"]A21[/TD]
[TD="class: xl63"]T485[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]A41[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]500[/TD]
[/TR]
[TR]
[TD="class: xl63"]T456[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]T485[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]T456[/TD]
[TD="class: xl63, align: right"]300[/TD]
[TD="class: xl63"]A31[/TD]
[TD="class: xl63"]T485[/TD]
[TD="class: xl63, align: right"]500[/TD]
[TD="class: xl63"]A31[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]T456[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"]A21[/TD]
[TD="class: xl63"]T485[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]A41[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]T456[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]T485[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

H2: A31

I2: =COUNTIF(C2:F10,H2)

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


=IF(ROWS(J$2:J2)<=$I$2,OFFSET(INDIRECT(TEXT(SMALL(IF($C$2:$F$10=$H$2,(ROW($C$2:$F$10)*10^5)+COLUMN($C$2:$F$10)),ROWS(J$2:J2)),"R0C00000"),0),0,-1),"")

Actually, we can avoid using the volatile functions OFFSET and INDIRECT...

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


=IF(ROWS(J$2:J2)<=$I$2,INDEX($A:$F,INT(SMALL(IF($C$2:$F$10=$H$2,(ROW($C$2:$F$10)*10^5)+COLUMN($C$2:$F$10)),ROWS(J$2:J2))/10^5),MOD(SMALL(IF($C$2:$F$10=$H$2,(ROW($C$2:$F$10)*10^5)+COLUMN($C$2:$F$10)),ROWS(J$2:J2)),10^5)-1),"")

Hope this helps!

 

[Marcelo Branco]

Or (using data sample provided by Domenic in post 2)

Formula in J2 copied down
=IF($I$2>=ROWS(J$2:J2),AGGREGATE(15,6,B$2:E$10/($C$2:$F$10=$H$2),ROWS(J$2:J2)),"")

M.