Formula to return error text when value not found in lookup table

Question

I am using a lookup formula to look up prices for services in a table. I need it to return error message text when a service name in column I doesn't exist in the Rates lookup table. Right now, it is returning the next highest value.

=VLOOKUP(I2,Rates!$A$2:$C$18,3)

Thanks, KM

Answer 1

I am using a lookup formula to look up prices for services in a table. I need it to return error message text when a service name in column I doesn't exist in the Rates lookup table. Right now, it is returning the next highest value.

=VLOOKUP(I2,Rates!$A$2:$C$18,3)

Thanks, KM

Why don't you set the 4th argument, range_lookup, to FALSE or 0?

=VLOOKUP(I2,Rates!$A$2:$C$18,3,0)

Using this syntax, if the lookup_value I2 is not found in Rates!A2:A18 then the formula will return the #N/A error.

If you would rather not get the #N/A error and would like some other message instead, then try something like this:

=IFERROR(VLOOKUP(I2,Rates!$A$2:$C$18,3,0),"Not Found")

Replace Not Found with a message of your choice.

--

Biff

Microsoft Excel MVP

Answer 2

I knew it was something simple. My brain's just dead. Thanks!

You're welcome. Thanks for the feedback!

--

Biff

Microsoft Excel MVP

Answer 3

I knew it was something simple. My brain's just dead. Thanks!