CODEWITHSUNDEEP
excelexcel-formula
Maezar
Maezar

Reputation: 11

My Index Match Doesn't work for value less than those in table

My Data]1

Hello all,

I am trying to match the value in B1 to a value in B3:B12 to return a value from column C. Everything is working fine and my match is correctly finding the lowest value in the list that is less than or equal to my entered data.

However, if the data entered is lower than the value in B3, I get #N/A.

My formula is:

=INDEX(B3:C12,MATCH(B1,B3:B12,1),2)

Can anyone help? When this is correct, any value in B1 that is less than value B3 should return C3. I could do it with a simple IF but this seems inelegant, and I can't understand why I need to.

Upvotes: 1

Views: 408

Answers (1)

user4039065
user4039065

Reputation:

Try,

=iferror(INDEX(c3:C12, MATCH(B1, B3:B12, 1)), c3)
'alternate
=INDEX(c3:C12, iferror(MATCH(B1, B3:B12, 1), 1))

Upvotes: 2

Related Questions