Excel lookup returning multiple values in a column

Question

Everyone -

I have seen numerous posts on this site similar to this, however with the way the data is laid out, none will work for what we are trying to accomplish.

I have a spreadsheet with columns having data B:DM. Row 2 designates whether a value in A2 presents itself in one of the columns and using a conditional formula sets the appropriate color and response, i.e. True = green, False = red.

Below row 2 (row 3) is the column "header" describing the data the header represents.

What I would like to have is cell A3 to be a list of all column headers that match when row 2 = "True". In other words, in the example image, A4=Z_D_CA_5000_KEY_PROC_TEST_D, A5=Z_D_CO_5000_CO-LEAD_D

Is this even possible? I appreciate your time and attention.

Answer 1

Sorry, I haven't the time to write a formula with exact particulars here, but consider the following:

1. You say you have a formula controlling the Conditional Formatting ("CFG")
2. Said formula will work cell-side just as easily as in the CF feature
3. It must already be written with appropriate relative references or it couldn't properly control your CF
4. You can set up a formula in A3 that tests the row three range against that formula (IF(B3:DM3=CF formula, B$2:DM$2&", ","")

and you have the basic idea. I have not tested that the formula needs {CSE} entry 'cause I'm not writing the actual formula you will write. But if it does, the do it, eh? Pretty sure you can have the formulas as standalones on each row, i.e.. no need for highlighting whatever, perhaps A3:A2003 and using {CSE}. Just do it for the row, then copy/fill down.

(People seem to do this a lot, know they have a formula in their CF, but completely forget they can put it into a cell too. Always remember, if there's a formula in CF, it works in cells too.)