Help with LOOKUP function

[ellenricca]

This function is in a workbook with 2 sheets. It _almost_ works
perfectly. These "C" columns in two different sheets
'2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column
in one of the sheets - '2004-2005'!D - contains a date associated
with the person's name from the C column of 2004-2005 sheet.

This formula is in the "D" column of Sheet 2005-2006.
=LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D)

The concept is for the formula to lookup the value (person's name) in
column C of 2005-2006 and compare it to the value (person's name) in
column C of sheet 2004-2005. If the names match, place the value (the
date) from column D in sheet 2004-2005 and place it in column D of
sheet 2005-2006. It matches pretty good....

The problem I am having is with the non-matches. I would like it to put
"N/A" or have it be blank in the D column of 2005-2006 if it does not
match, however, instead it is putting the date from the record above
it.

I have looked in help for LOOKUP, VLOOKUP, INDEX, and MATCH, and have
re-worded the formula but I get invalid formulas and no results. Any
ideas are greatly appreciate....

Thanks!
Ellen

 

[L. Howard Kittle]

Hi Ellen,

Try something like this. You can put whatever between the ""'s. Something
like Not Found or an 0 or the "" returns a blank looking cell.

if(iserr(yourformula)),""(yourformula))

HTH
Regards,
Howard

 

[ellenricca]

Thanks for the response. I used this formula...

=IF(ISERR(LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D)),"not
found",(LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D)))

but the incorrect results were the same. The non-matches get the date
of the matched record above it, instead of displaying "not found"

When I changed LOOKUP to either VLOOKUP or INDEX then all items are
returned as "not found". I'm sure I am missing some logic here..any
further help is appreciated..

Ellen

 

[L. Howard Kittle]

Take a look at the 4th argument, perhaps try FALSE or 0, and or sort in
asscending order.

 

[ellenricca]

Thanks for the response but that did not work. As it stands now, this
is the formula:
=IF(ISERR(LOOKUP('2005-2006'!$C:$C,'2004-2005'!$A:$A,'2004-2005'!$B:$B)),"not
found",(LOOKUP('2005-2006'!$C:$C,'2004-2005'!$A:$A,'2004-2005'!$B:$B)))

Again, when no match is found, Excel is just grabbing the previous
records value and insterting that instead of displaying "not found".

This is really starting to drive me crazy!! Would anyone be willing to
have me email my workbook so they could see more clearly what I am
trying to accomplish?? Thanks so much for all your help...

Ellen

 

[L. Howard Kittle]

I would take a look at it.

(e-mail address removed)

 

[ellenricca]

Thanks Howard! The formula you sent me worked perfectly:

=IF(ISNA(VLOOKUP(A2,'2004-2005'!$A$2:$D$10,4,0)),"Not
found",VLOOKUP(A2,'2004-2005'!$A$2:$D$10,4,0))

Ellen