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
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