View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Return of blank cell if lookup fails

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


--

HTH

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


"TimM" wrote in message
...
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