>Conditional Formating to hilight the index match result...

pedie

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Hi, the formula below works perfect for me & i hope it is correct. K, what 'm looking is that i put a formula in conditional formating and it hilght where the formula result is from...("B$2:$NB$15") i have data that looks almost alike eg X, Y, Q etc...

Thanks for helping.

Pedie

In the huge tabe
PHP: 
'=INDEX($B$2:$NB$15,MATCH($B$22,$A$2:$A$15,0),MATCH(B23,$B$1:$NB$1,0))
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Perhaps:
Code: 
=ADDRESS(MATCH($B$22,$A$2:$A$15,0),MATCH(B23,$B$1:$NB$1,0))
<code style="white-space: nowrap;"><code>
</code></code>
 
Upvote 0
I would assume from what you say that the formula

=INDEX($B$2:$NB$15,MATCH($B$22,$A$2:$A$15,0),MATCH(B23,$B$1:$NB$1,0))


Is already in use in the worksheet to find a value in the table, in which case why not just reference the result cell for the conditional format.

For example, if the fomula above is in cell B24 then your CF formula would simply be =$B$24 no need to overcomplicate things.
 
Upvote 0
Delan, thanks but i dont know why it is not hilighting..have youtested this?i'm not sure if 'm doing it right...:confused:
 
Upvote 0
There could be many match by i want the formating to hilight where row value and column value cross...
'm not sure it is still not working..:confused:
<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=448><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #272727; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=64>1x2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=64>1x3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=64>1x4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=64>1x5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=64>1x6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=64>1x7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #272727; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17>A1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #272727; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17>A2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>x</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #272727; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17>A3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #272727; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17>A4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffc000; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #272727; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17>A5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>x</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #272727; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17>A6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #272727; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17>A7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #272727; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17>A8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #272727; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>z</TD></TR></TBODY></TABLE>
 
Upvote 0
In that case try

=CELL("address",A1)=ADDRESS(MATCH($B$22,$A$2:$A$15,0),MATCH($B$23,$B$1:$NB$1,0))
 
Upvote 0
Jason...this is perfect. Exactly what i'm looking for.

Thanks alot!:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Conditional Formatting with a Formula
Replies
4
Views
78
JDSOuth49
J
E
  • Question
How to recognize text strings in INDEX-MATCH
Replies
5
Views
389
dfsobral
D
K
  • Solved
Conditional Formatting
Replies
4
Views
276
kevin9999
K
D
  • Solved
INDEX-MATCH with a vertical lookup
Replies
4
Views
148
Darren_workforce
D
K
  • Solved
Conditional formatting
2
Replies
17
Views
633
awoohaw
awoohaw

Forum statistics

Threads
1,215,639
Messages
6,125,970
Members
449,276
Latest member
surendra75

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top