I have one table of data (A) with data values, and a second table (B) which serves as a key that categorises/groups those entries into categories.

I am trying to write a cell formula that will list the category that corresponds to the data value in (A).

Some of the entries in the data table are empty, i.e. have no input data.

Case in point: A13 has an empty cell, according to table (B), I specifically want the blank cell to be categorised as "Other". At the moment the sheet thinks cells like these are "null" and not "blank" and therefore returns #N/A.

I have tried adding an IFERROR function, which works at this stage but does not suit the scenario where a new data entry is entered as that will initially show as "Other" too when it should be something else, so I want to avoid using:

=IFERROR(INDEX(D2:D13,MATCH(A2:A13,E2:E13,0)),"Other")

Current cell formula for col B:

=INDEX(D2:D13,MATCH(A2:A13,E2:E13,0))

Please can you help? I've found plenty of articles on how to ignore blank/empty cells, but nothing to include them.

Many thanks! OH

Shared link of problem

Screenshot of problem

CodePudding user response：

use ifna like:

=IFNA(INDEX(D2:D13, MATCH(A2:A13, E2:E13, 0)))

update:

=INDEX(IF(A2:A13="",,IFERROR(INDEX(D2:D13,MATCH(A2:A13,E2:E13,0)),"Other")))