0

I have the following query :

My input table is

enter image description here

Basically I want to lookup columns for each ID. Value3>Value2>Value1

If value3 and value column has data then ouput should show ID having value3

If value3 has data present then populate output as Value3 , if value2 and value3 have data , then also it should populate value3

Output:

enter image description here

Is there formula I can write to retrieve this value?

2
  • Is your test based on the amounts in the value columns or only the presence of an amount in the column? For your output table, what you want is the column heading, not the value?
    – fixer1234
    Nov 19, 2014 at 22:48
  • only presence of value in the column. Amount has no significance
    – misguided
    Nov 19, 2014 at 22:51

2 Answers 2

1

If it is just three value columns, a simple way to do it would be IF tests. Say the table you show starts in A1 (A1="ID" heading). The formula for the first output value would be:

    =IF(ISBLANK(D2),IF(ISBLANK(C2),$B$1,$C$1),$D$1)

Copy that down the output column. (This assumes every row in the data table is represented by a row in the output table and both have the ID column in the same order.)

1

Here’s a more general solution.  I’ll assume that you have five “value” columns (B through F) and you want the result in Column G.  Type this formula into cell G2:

=OFFSET($B$1, 0, MAX(COLUMN(B2:F2)*NOT(ISBLANK(B2:F2)))-2)

and type Ctrl+Shift+Enter.  That makes it an “array formula”; it will show up in the formula bar with braces ({}) around it.  Now drag it down as many rows as you want:

                        demo spreadsheet

  • ISBLANK() checks whether a cell is blank (TRUE if it is, FALSE if it isn’t).
  • NOT(…) inverts the logic; now we have TRUE if the cell has (non-blank) data and FALSE if it doesn’t have data. 
    It turns out the TRUE is actually the value 1 and FALSE is actually 0.
  • COLUMN(B2:F2)*(the above) takes the column number of each cell in the range and multiplies it by 1 if the cell has data and 0 if it doesn’t.  This results in the column number (×1) if the cell has data, and 0 (column number × 0) if it doesn’t.  For example, for Row 3, this is {2,0,4,0,0}, because the second (B) and fourth (D) columns have data.
  • MAX(…) picks the largest of these; i.e., the number of the right-most column where there is data.
  • =OFFSET($B$1, 0, (the above)-2) finds the cell that is 0 rows below and MAX(COLUMN(B2:F2)*NOT(ISBLANK(B2:F2)))-2 columns to the right of cell B1.  We subtract 2 from the column number because B1 is in column 2, so, when MAX(COLUMN(B2:F2)*NOT(ISBLANK(B2:F2))) evaluates to 2 (as it does on Row 2), we want to go 0 columns to the right of cell B1.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .