excelexcel-formulams-office

Is there a way to find first non zero value for a lookup value in excel


can anyone please help me find first non zero value for a lookup in excel. Example, in the below table, when I lookup for 1, with respect to column2, I should get value 'b', not blank or #NA.

enter image description here

I found answers here to get the column headers, but I want the actual value.


Solution

  • If you have Excel 365 you can use this formula:

    =TAKE(FILTER(B2:B5,(A2:A5=1)*(B2:B5<>"")),1)

    It filters column2 for non-empty values where value in column 1 equals 1.

    And then takes the first result.