Lookup returns blank if no match

[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

 

[Biff]

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

 

[Domenic]

Try...

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

Hope this helps!

 

[Guest]

I'm just entering new data in the field (to test the formula mostly) by
typing in C1.

 

[Biff]

Post the *EXACT* formula that gives you a #REF! error.

Biff

 

[Guest]

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.

 

[Biff]

Ok, good deal!

Biff

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.

 

[Guest]

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 D110, 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?

 

[Domenic]

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!

 

[Guest]

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.