Index-match function returns #N/A when number is used as text

Question

I'm using index-match formula for extracting data from a table("DETAIL"). The formula works well when cell B6 contains number in General/Number format but returns to #N/A when cell B6 contains number in text format. I cant convert it as number because it may be started from zero (0) which disappears when number is used as general format. My formula is

=INDEX(DETAIL,MATCH(B6,BMID,0),MATCH(D5,DATA_R,0))

Please help me to find a valid solution of the problem.

in what cell are you using the formula and what is the end result you want? — krib
Then format the data where you're matching against as text as well. — Luuklag

Mrig Mrig · Accepted Answer · 2017-08-28T12:59:55

You can use

=INDEX(DETAIL,MATCH(Value(B6),BMID,0),MATCH(D5,DATA_R,0))

I don't see any harm in converting B6 to number using VALUE(). See image for reference.

Index-match function returns #N/A when number is used as text

1 Answers