Return of blank cell if lookup fails

[Guest]

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.

 

[Richard Buttrey]

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.

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
__

 

[Bob Phillips]

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


--

HTH

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

 

[PCLIVE]

Try this:

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

Regards,
Paul

 

[Phillycheese5]

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