Lookup returns blank if no match

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list of names in A1:A25, with corresponding dates in B1:B25 in
worksheet 1. In worksheet 2, I have a cell where I enter a name (C1) and a
cell where a date will go (D1). I want to enter the name in C1, have it go to
the list of names and look for a match. If a match is found, enter the
corresponding date into cell D1, if no match is found, leave it blank.

LOOKUP puts in the next item if no match. I tried VLOOKUP with ISNA around
it, but get a #REF if I try to change the data in C1
 
get a #REF if I try to change the data in C1

How are you trying to change the data? If you type in a new entry or use a
drop down it should work. If you are cutting/pasting or drag and drop then
you'll get a #REF! error.

Try this:

=IF(COUNTIF(Sheet1!A1:A25,INDIRECT("C1")),VLOOKUP(INDIRECT("C1"),Sheet1!A1:B25,2,0),"")

Cut/paste and drag/drop won't affect that formula.

Biff
 
Try...

=IF(ISNA(VLOOKUP(C1,'Sheet1'!$A$1:$B$25,2,0)),"",VLOOKUP(C1,'Sheet1'!$A$1
:$B$25,2,0))

Hope this helps!
 
I think I found the error ... I had originally set the formula like the one
Domenic posted, but I didn't include both columns in my table_array argument.
I put the correct range in and it works fine now.
 
Ok, good deal!

Biff

Nolene said:
I think I found the error ... I had originally set the formula like the one
Domenic posted, but I didn't include both columns in my table_array
argument.
I put the correct range in and it works fine now.
Click to expand...
 
This worked great. But now I have a follow up for a modification:

Sheet 1: List of names in A1:A25, corresponding dates in B1:B25 - List of
6-digit alphanum codes in C1:C10, corresponding code in D1:D10, some lines
have another code ("ECY") in column E (not all together -- 5 will have, 12
will not, 3 will have, etc). If needed I can put another code, say NNN, in
those that don't have ECY.

Sheet 2: C1 - Keyed Name; D1 - Keyed date; E1 - lookup date; F1 - Keyed Code

I want to enter name in C1, lookup name in Sheet 1 Col A for a match, if
there's a match, then enter the date from col B into E1. If there is no
match, look at F1 and compare that to the codes on Sheet 1 Col C. If that
code has ECY in col E, then look at the date entered in D1 and put 12/31/yy
of whatever year is in D1 into E1, if it doesn't have ECY (or has NNN), leave
blank.

Is this just way too complicated or is it doable?
 
It's unclear whether D1 contains a year, such as 2006, or a date, such
as 12/31/06. If the former, replace...

DATE(YEAR(D1),12,31)

with

DATE(D1,12,31)

in the following formula...

=IF(ISNUMBER(MATCH(C1,Sheet1!A1:A25,0)),INDEX(Sheet1!B1:B25,MATCH(C1,Shee
t1!A1:A25,0)),IF(ISNUMBER(MATCH(F1,Sheet1!C1:C25,0)),IF(INDEX(Sheet1!E1:E
25,MATCH(F1,Sheet1!C1:C25,0))="ECY",DATE(YEAR(D1),12,31),""),""))

Note that the formula will also return a blank when there's no match for
F1.

Hope this helps!
 
Man you're the BEST. I have to get 800 - 1000 boxes of files indexed and it
will save sooooo much time having the spreadsheet lookup stuff rather than
the indexers always having to refer to a separate piece of paper to lookup
info that needs to be keyed.

BTW the date was mm/dd/yyyy.
 
Back
Top