Conditional Formatting base on Cell result

[Chand1987]

Good Morning Members,

I have a Large file in excel and am using a formula to return a match value like the one below.

below array formula will return the Data based on the match. with this formula, it will return the Name of People from "AllMasterData" Sheet to "Track Data" Sheet . and i have the company name in the "AllMasterData" of Name of People.

Now I want to highlight the individual cell based on the Company Name in "AllMasterData" which matches with the Name of People.

Result Cell - Track Data

Master Data

=INDEX(AllMasterData!$AC$4:$AC$1020,SMALL(IF(ISNUMBER(MATCH(AllMasterData!$I$4:$I$1020,BI37,0)),MATCH(ROW(AllMasterData!$I$4:$I$1020),ROW(AllMasterData!$I$4:$I$1020)),""),ROWS($A$1:$A$1)))
=INDEX(AllMasterData!$AC$4:$AC$1020,SMALL(IF(ISNUMBER(MATCH(AllMasterData!$I$4:$I$1020,BI37,0)),MATCH(ROW(AllMasterData!$I$4:$I$1020),ROW(AllMasterData!$I$4:$I$1020)),""),ROWS($A$1:$A$2)))
=INDEX(AllMasterData!$AC$4:$AC$1020,SMALL(IF(ISNUMBER(MATCH(AllMasterData!$I$4:$I$1020,BI37,0)),MATCH(ROW(AllMasterData!$I$4:$I$1020),ROW(AllMasterData!$I$4:$I$1020)),""),ROWS($A$1:$A$3)))
=INDEX(AllMasterData!$AC$4:$AC$1020,SMALL(IF(ISNUMBER(MATCH(AllMasterData!$I$4:$I$1020,BI37,0)),MATCH(ROW(AllMasterData!$I$4:$I$1020),ROW(AllMasterData!$I$4:$I$1020)),""),ROWS($A$1:$A$4)))

Thank you

 

[James006]

Hi,
You can test following formula :

=ISNA(MATCH(A1,MasterList,0))

 

[Chand1987]

Hi,
You can test following formula :

=ISNA(MATCH(A1,MasterList,0))

Thank you for your response!
Can you please explain where to Add this formula? as I mentioned, I want to use conditional formatting based on the company Name with match like below.


AllMasterData Sheet.

Track Data Sheet - Result required
Formula Uses
=INDEX(AllMasterData!$AC$4:$AC$1020,SMALL(IF(ISNUMBER(MATCH(AllMasterData!$I$4:$I$1020,BI37,0)),MATCH(ROW(AllMasterData!$I$4:$I$1020),ROW(AllMasterData!$I$4:$I$1020)),""),ROWS($A$1:$A$1)))
=INDEX(AllMasterData!$AC$4:$AC$1020,SMALL(IF(ISNUMBER(MATCH(AllMasterData!$I$4:$I$1020,BI37,0)),MATCH(ROW(AllMasterData!$I$4:$I$1020),ROW(AllMasterData!$I$4:$I$1020)),""),ROWS($A$1:$A$2)))
=INDEX(AllMasterData!$AC$4:$AC$1020,SMALL(IF(ISNUMBER(MATCH(AllMasterData!$I$4:$I$1020,BI37,0)),MATCH(ROW(AllMasterData!$I$4:$I$1020),ROW(AllMasterData!$I$4:$I$1020)),""),ROWS($A$1:$A$3)))
=INDEX(AllMasterData!$AC$4:$AC$1020,SMALL(IF(ISNUMBER(MATCH(AllMasterData!$I$4:$I$1020,BI37,0)),MATCH(ROW(AllMasterData!$I$4:$I$1020),ROW(AllMasterData!$I$4:$I$1020)),""),ROWS($A$1:$A$4)))

 

[James006]

Hi again,

The suggested formula is the Formula for your conditional formatting ... as long as you have created a Named Range (with the name: MasterList )

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Conditional Formatting base on Cell result

Please help me to get this result I have a Large file in excel and am using a formula to return a match value like the one below. below array formula will return the Data based on the match. with this formula, it will return the Name of People from "AllMasterData" Sheet to "Track Data" Sheet...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Chand1987]

Hi again,

The suggested formula is the Formula for your conditional formatting ... as long as you have created a Named Range (with the name: MasterList )

I am not sure if I am doing something wrong but this formula is highlighting all cells regardless company-wise. when it sees data which matches in Master Lists, all cells are highlighted.


Thank you,