I have a complex lookup problem. I am trying to extract the column header information from qualifying columns of a data table, based on the contents of a row found via VLOOKUP.
The images below show Sheet1 (source and destination for the lookup results), and Sheet2 (lookup data table). Column A of both sheets contains the code I'm matching on.
I'll use as an example, the first row in Sheet1, which shows code
2018-K-004. I look it up on Sheet2 and find it in the fourth data row.
Now I want to examine the columns on the right, starting with D. One or more of them may contain a value greater than zero (the qualifying criterion), for that row.
For every column containing a value greater than zero in that row, I want to retrieve the column header. These will go on Sheet1, starting in column C, for the source row (row 1 in this example).
Each qualifying column header will go in the successive column on Sheet1 (e.g., first qualifying column header in column C, next qualifying header in column D, etc.)
In this example,
2018-K-004 has a value greater than zero only in column D of Sheet2, so that column header,
DSG would go in C1 on Sheet1.
2018-K-003 isn't shown on the Sheet1 image, but if that was the lookup target, it would be found on the third data row of Sheet2. That one has nine qualifying (i.e., greater than zero) columns, so for its row on Sheet 1, column C would contain
DSG, column D would contain
EBG, column E would contain
RRG, and so forth.
I have many of the pieces, but can't figure out how to put the whole thing together. I can look up the code and find the row on Sheet2. I found this formula to get the top cell for a specific column / cell range:
But that doesn't identify whether the column qualifies based on the row value of the code lookup. It also doesn't consolidate the qualifying headers on Sheet1.
How can I accomplish this task?