CODEWITHSUNDEEP
excelworksheet-function
Ron L
Ron L

Reputation: 51

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

Upvotes: 0

Views: 1660

Answers (2)

Gary's Student
Gary's Student

Reputation: 96791

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

Upvotes: 1

user4039065
user4039065

Reputation:

You cannot lookup a blank cell. Use IFERROR to find the first blank with AGGREGATE in the target if you receive an #N/A.

=INDEX(B9:B12, iferror(MATCH(A1,A9:A12,0), aggregate(15, 6, row($1:$4)/not(len(A9:A12)), 1)))

row($1:$4) is the position within B9:B12 that you are returning to the INDEX.

Upvotes: 0

Related Questions