Thread: help with a Lookup formula

hi all,

I need to create a Lookup formula which will create a certain answer should a value NOT be found.

I have a long list of data on my left column, I then want to enter some data in another column and get the Lookup function just to simply say wether the data I just entered is either in the left column or that it doesn't appear. I suspect I will have to use an IF formula, but I have not much experience with them. Can anyone lend me a hand?

You do need an IF function. You want to disable "range lookup" on the lookup function, so that it doesn't find an in-between value, and then you want to use the ISERROR() function. ISERROR returns true or false, based on the parameter. If the parameter is something like #N/A, it will return true.

"IF" is a simple function with three parameters:

=IF( Statement that is TRUE OR FALSE, Value if True, Value if False)

OR you can think of it as If <blank>, then..., else...

=IF(<blank>, then..., else... )

In your case, the <blank> should be ISERROR(<lookup function as normal>) and then and else should be what you want to display. If you want the cell to be blank use two double quotes "", for the Then or Else.