Index Match (with multiple matches across multiple columns)

[qrtback10]

Below is the formula that I'm currently using. When entered it returns error message: "Did not find value '1300' in MATCH evaluation". What am I doing wrong? The 'Bedrooms' column (S2) should equal "3 BD From". Please advise. Thank you!

=ARRAYFORMULA(INDEX(H1:L1,MATCH(1300,INDEX(H1:L,1),0),MATCH(R2,O2:O,0)))

0 BD From

1 BD From

2 BD From

3 BD From

4 BD From

Marker Type

Notes

Code

Rating

Code

Bedrooms

Rent

ltblu_blank

CC-ALL-2929

CC

CC-GRE-1001

ltblu_blank

CC-ALL-2901

AA

ltblu_blank

CC-ALL-1991

DD

ltblu_blank

CC-COL-2222

AA

buildings

CC-COL-1201

AA

ltblu_blank

CC-GRE-1001

AA

ltblu_blank

CC-GRE-800

BB

ltblu_blank

CC-GRE-1900

BB

<tbody>
[TD="align: right"]1332[/TD]
[TD="align: right"]1448[/TD]
[TD="align: right"]2652[/TD]

[TD="bgcolor: #ffff00"][/TD]
[TD="align: right"]1300[/TD]

[TD="align: right"]1071[/TD]
[TD="align: right"]1259[/TD]
[TD="align: right"]1755[/TD]

[TD="align: right"]831[/TD]
[TD="align: right"]1015[/TD]
[TD="align: right"]1308[/TD]

[TD="align: right"]710[/TD]
[TD="align: right"]867[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]1785[/TD]

[TD="align: right"]697[/TD]
[TD="align: right"]812[/TD]
[TD="align: right"]1064[/TD]

[TD="align: right"]950[/TD]
[TD="align: right"]1159[/TD]
[TD="bgcolor: #ffff00, align: right"]1300[/TD]

[TD="align: right"]1265[/TD]
[TD="align: right"]1463[/TD]
[TD="align: right"]2341[/TD]

[TD="align: right"]679[/TD]
[TD="align: right"]823[/TD]
[TD="align: right"]972[/TD]

</tbody>

 

[Domenic]

Try...

=INDEX(H1:L1,MATCH(T2,INDEX(H2:L9,MATCH(R2,O2:O9,0),0),0))

Hope this helps!

 

[qrtback10]

Man, you guys are good! Thanks Domenic! That was exactly what I needed. Will save this and remember to nest INDEX within MATCH. Thanks!

 

[Domenic]

You're very welcome!