How to extract conditionally-formatted "highlighted" cell data

I have a workbook with 2 sheets. Sheet2 is imported data. Sheet1 is Sheet2 data used in various functions. It is also conditionally-formatted to return a highlighted cell.

I would like to be able to search for highlighted cells in any given row of data and return the cell value and column heading.

I tried using Ctrl+F with the pattern type selected. No luck. I have no clue if this is even doable using Excel functions or VBA code.

A small sample workbook is attached. Any suggestions are appreciated!

Your RANK formulae (e.g. this in B2: =RANK(Sheet2!B2,Sheet2!B2:B8,1) ) are using ranges which will adjust when copied down. Is this what you want? It would be more usual to use this:

=RANK(Sheet2!B2,Sheet2!B$2:B$8,1)

so that when you copy it down the range remains the same.

As your CF condition is looking for the Top 1, then you can use the MAX function to determine if the value should be displayed, e.g. in C21:

=IF(VLOOKUP(INDIRECT($C$19),$A$2:$D$8,COLUMNS($A:B),0)=MAX(B$2:B$8),B$1,"")

and in C22:

=IF(VLOOKUP(INDIRECT($C$19),$A$2:$D$8,COLUMNS($A:B),0)=MAX(B$2:B$8),VLOOKUP(INDIRECT($C$19),$A$2:$D$8,COLUMNS($A:B),0),"")

Copy these across to the next two columns.

Hope this helps.

Pete

Originally Posted by Pete_UK

Your RANK formulae (e.g. this in B2: =RANK(Sheet2!B2,Sheet2!B2:B8,1) ) are using ranges which will adjust when copied down. Is this what you want? It would be more usual to use this:

=RANK(Sheet2!B2,Sheet2!B$2:B$8,1)

so that when you copy it down the range remains the same.

As your CF condition is looking for the Top 1, then you can use the MAX function to determine if the value should be displayed, e.g. in C21:

=IF(VLOOKUP(INDIRECT($C$19),$A$2:$D$8,COLUMNS($A:B),0)=MAX(B$2:B$8),B$1,"")

and in C22:

=IF(VLOOKUP(INDIRECT($C$19),$A$2:$D$8,COLUMNS($A:B),0)=MAX(B$2:B$8),VLOOKUP(INDIRECT($C$19),$A$2:$D$8,COLUMNS($A:B),0),"")

Copy these across to the next two columns.

Hope this helps.

Pete

Thanks Pete! Your formulas work quite well. Followup question: Is it be possible to grab the (nth) Max numbers in a column? Such as the 2 highest or 3 highest?

Instead of using MAX you can use the LARGE function, as in:

LARGE(B$2:B$8,2) and LARGE(B$2:B$8,3)

Hope this helps.

Pete

Originally Posted by Pete_UK

Instead of using MAX you can use the LARGE function, as in:

LARGE(B$2:B$8,2) and LARGE(B$2:B$8,3)

Hope this helps.

Pete

Works like a charm. Thanks again Pete.

You're welcome - thanks for the rep.

Pete