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?

Sheet1: sheet1

Sheet2: sheet2

  • It isn't clear what your asking. VLOOKUP searches vertical information in a range you specify to find a match. If you want the top row, it's the first cell in the range you're asking it to search. Can you add a mockup or screenshot of a table, and use that to explain what you would search for and where, and what result you would expect? – fixer1234 May 22 '18 at 3:18
  • @fixer1234 I've add some screenshoot and explanation. – Vahn May 22 '18 at 3:46
  • Better, but the logic still isn't clear. The A1 value gets you to a row in Sheet2. It appears you are interested in the collection of columns on the right, or every answer would be DSG if there was a value in that column. In this case only one column on the right had a value. It was 1, and the column heading was DSG. What determines which column has the value you are interested in? What if all columns have 0? If A1 contained 2018-K-001, most of the right columns have a value, including duplicate values. This looks like a second (horizontal) lookup of something to pick a column. – fixer1234 May 22 '18 at 5:31
  • @fixer1234 if the parameter for look up is 2018-K-001 the data displayed in sheet1 should be the top row which is greater than 0. It's added in the next right column in sheet1 – Vahn May 22 '18 at 6:02
  • Eight columns have values greater than zero. What result would you be looking for? – fixer1234 May 22 '18 at 6:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.