Return of blank cell if lookup fails

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.

--
Thanks
TimM

=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)


"TimM" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> --
> Thanks
> TimM

On Wed, 23 Nov 2005 07:48:31 -0800, "TimM"
<[email protected]> wrote:

>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
__

Try this:

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

Regards,
Paul

"TimM" <[email protected]> wrote in message
news:[email protected]...
>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.
>
> --
> Thanks
> TimM

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