Return of blank cell if lookup fails

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TimM - 23 Nov 2005 15:48 GMT

I have a lookup formula and I want to do two things with it.
Formula =LOOKUP(C251,Data!B:B,Data!A:A)
When cell C251 is blank it currently returns #N/A, I would like to return a
blank cell.
Next when a number entered into cell C251 is not found in the specified
range (Data!B:B) it returns the next lowest number. (Cell Data!B:B is a list
of 7 digit numbers, but some cells contain words. Cell Data!A:A is a list of
the names assigned to the number in Data!A:A) I would like it to return just
a blank cell.

I guess it's the same problem with both, I want it be blank unless it
matches something in the list.

Signature

Thanks
TimM

Reply to this Message

Richard Buttrey - 23 Nov 2005 16:26 GMT

>I have a lookup formula and I want to do two things with it.
>Formula =LOOKUP(C251,Data!B:B,Data!A:A)
[quoted text clipped - 8 lines]
>I guess it's the same problem with both, I want it be blank unless it
>matches something in the list.

Wrap it up wiath an IF(ISNA) and include another test. i.e.

=IF(ISNA(OR(VLOOKUP(C251,Data!B:B,1,FALSE),LOOKUP(C251,Data!B:B,Data!A:A))),"",LOOKUP(C251,Data!B:B,Data!A:A))

HTH

Richard Buttrey
__

Reply to this Message

Bob Phillips - 23 Nov 2005 16:26 GMT

=IF(ISNA(MATCH(C251,Data!B:B)),"",INDEX(Data!A:A,MATCH(C251,Data!B:B,Data!A:
A)))

Signature

HTH

RP
(remove nothere from the email address if mailing direct)

> I have a lookup formula and I want to do two things with it.
> Formula =LOOKUP(C251,Data!B:B,Data!A:A)
[quoted text clipped - 8 lines]
> I guess it's the same problem with both, I want it be blank unless it
> matches something in the list.

Reply to this Message

PCLIVE - 23 Nov 2005 16:31 GMT

Try this:

=IF(C251="","",LOOKUP(C251,Data!B:B,Data!A:A)

Regards,
Paul

>I have a lookup formula and I want to do two things with it.
> Formula =LOOKUP(C251,Data!B:B,Data!A:A)
[quoted text clipped - 12 lines]
> I guess it's the same problem with both, I want it be blank unless it
> matches something in the list.

Reply to this Message

Phillycheese5 - 23 Nov 2005 16:40 GMT

You can use something like this to avoid the N/A's :

=IF(ISNA(LOOKUP ARGUMENT),"",LOOKUP ARGUMENT))

It basically says if the lookup argument is N/A, then put "" (shows up
as blank), otherwise if it's not N/A, then use the argument.

I either use the vlookup or the hlookup. If you can switch your data
around, then you can specify using the exact value in the argument.

=VLOOKUP(A1,B1:C100,2,FALSE)

It says to find A1 in the table B1:C100 (what you put in A1 will only
be searched for in the range B1:B100, the left-most column of the
table), and give me the second cell value to the right. The formula
counts B as one, then C as two. The "FALSE" tells it to only look for
A1 as an exact match.

Hope it helps.
Phillycheese

Signature

Phillycheese5

Reply to this Message