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.

T456	6	A31	T485	4	A11
T456	4	A21	T485	3	A41
T456	6		T485	6

<tbody>
</tbody>

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

T456	100	A31	T485	4	A11		A31	5	100
T456	4	A21	T485	400	A31				400
T456	6		T485	6					200
T456	200	A31	T485	4	A11				300
T456	4	A21	T485	3	A41				500
T456	6		T485	6
T456	300	A31	T485	500	A31
T456	4	A21	T485	3	A41
T456	6		T485	6

<tbody>
</tbody>

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.