Conditional formatting with INDEX & MATCH

[Mazbuka]

I'm learning INDEX/MATCH & have a little problem with conditional formatting

Below example looking at air fares by airline to various destinations, in cell D10 I return the correct cost based on the selections of airline & destination in B10 & C10, it works fine:

=INDEX($B$3:$F$7,MATCH($B$10,$A$3:$A$7,0),MATCH($C$10,$B$2:$F$2,0))


However, if I want to apply conditional formatting to B3:F7 and use the same formula, so starting at B3 and pasting format down & across to F7

=B3=INDEX($B$3:$F$7,MATCH($B$10,$A$3:$A$7,0),MATCH($C$10,$B$2:$F$2,0))

As F4 & F7 have the same cost, each are highlighted when my selection is Amsterdam & Qantas (or Manchester & Qantas) as it's fomatting based on the result, 1,896...

How do I get it to only format the cell at the selected intersection?


[TABLE="width: 480"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]ETIHAD[/TD]
[TD]BA[/TD]
[TD]AIR FRANCE[/TD]
[TD]EMIRATES[/TD]
[TD]QANTAS[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]DUBLIN[/TD]
[TD="align: right"]1,850[/TD]
[TD="align: right"]1,733[/TD]
[TD="align: right"]1,800[/TD]
[TD="align: right"]1,955[/TD]
[TD="align: right"]2,013[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]MANCHESTER[/TD]
[TD="align: right"]1,974[/TD]
[TD="align: right"]1,712[/TD]
[TD="align: right"]1,936[/TD]
[TD="align: right"]1,821[/TD]
[TD="align: right"]1,896[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]LONDON[/TD]
[TD="align: right"]1,723[/TD]
[TD="align: right"]2,003[/TD]
[TD="align: right"]2,002[/TD]
[TD="align: right"]2,112[/TD]
[TD="align: right"]1,982[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]PARIS[/TD]
[TD="align: right"]1,715[/TD]
[TD="align: right"]1,814[/TD]
[TD="align: right"]2,021[/TD]
[TD="align: right"]1,888[/TD]
[TD="align: right"]2,061[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]AMSTERDAM[/TD]
[TD="align: right"]2,053[/TD]
[TD="align: right"]2,012[/TD]
[TD="align: right"]1,743[/TD]
[TD="align: right"]2,114[/TD]
[TD="align: right"]1,896[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]FLY TO[/TD]
[TD]AIRLINE[/TD]
[TD]COST[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]AMSTERDAM[/TD]
[TD]QANTAS[/TD]
[TD="align: right"]1,896[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ClaireS]

I think you're getting over-complicated with INDEX/Match. This seems to work fine for me:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=AND($A3=$B$10,B$2=$C$10)[/FONT]

 

[Mazbuka]

Ha, that was really a case of me not seeing the woods for the trees! Such a simple fix, thanks Claire.

 

[ClaireS]

Happy to help