Search code examples
excelworksheet-function

Index match returning N/A when lookup value is null

I have a summary sheet in a workbook that allows a user to enter in a 3 digit ID and some summary data and a chart populates. In the source data, the ID for the totals row is blank. So, when the lookup value is blank (no 3 digit ID is entered) I expected the Index Match formula to return the values corresponding to a blank cell in the lookup array, but it doesn't. How can I fix this?

Sampling of the data:

 ID   March    April
111   10         15
222   15         10
333   10         10
      35         35

Formula used: 

=INDEX(B9:B12,MATCH(A1,A9:A12,0))

Where A1 is the lookup value

Solution

  • Say we have data like:

    enter image description here

    and we want to enter the name in A1 and retrieve the age in B1 and also accommodate the blank in column E.

    In B1 enter:

    =IF(A1="",INDEX(F:F,MATCH(TRUE,INDEX(ISBLANK(E1:E30),0,0),0)),VLOOKUP(A1,E2:F21,2,FALSE))

    enter image description here