Lookup returns blank if no match
 

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

Lookup returns blank if no match
 

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

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

Lookup returns blank if no match
 

Try...

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

Hope this helps!

In article ,
Nolene wrote:

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

Lookup returns blank if no match
 

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

"Biff" wrote:

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

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

Lookup returns blank if no match
 

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

Biff

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

"Biff" wrote:

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

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

Lookup returns blank if no match
 

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" wrote:

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

Biff

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

"Biff" wrote:

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

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

Lookup returns blank if no match
 

Ok, good deal!

Biff

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

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

Biff

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

"Biff" wrote:

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

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

Lookup returns blank if no match
 

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?


"Domenic" wrote:

Try...

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

Hope this helps!

In article ,
Nolene wrote:

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

Lookup returns blank if no match
 

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(Shee t1!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!

In article ,
Nolene wrote:

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?


"Domenic" wrote:

Try...

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

Hope this helps!

In article ,
Nolene wrote:

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

Lookup returns blank if no match
 

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.

"Domenic" wrote:

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(Shee t1!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!

In article ,
Nolene wrote:

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?


"Domenic" wrote:

Try...

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

Hope this helps!

In article ,
Nolene wrote:

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