VLookup and #N/A

[Alicia]

I am trying to return a blank when my lookup value is not in either my
Revised or Projected range.

=IF(ISNA(VLOOKUP($B$3,Revised,2,FALSE)),VLOOKUP($B$3,Projected,2,FALSE),VLOOKUP($B$3,Revised,2,FALSE))

Any suggestions?

Alicia

 

[T. Valko]

Try this:

=IF(ISNA(VLOOKUP(B3,Revised,2,0)),IF(ISNA(VLOOKUP(B3,Projected,2,0)),"",VLOOKUP(B3,Projected,2,0)),VLOOKUP(B3,Revised,2,0))

 

[Bob Phillips]

=IF(NOT(ISNA(VLOOKUP($B$3,Revised,2,FALSE))),VLOOKUP($B$3,Projected,2,FALSE),
IF(ISNA(VLOOKUP($B$3,Revised,2,FALSE)),"",VLOOKUP($B$3,Revised,2,FALSE)))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Don Guillett]

try
=IF(ISNA(VLOOKUP(B3,IF(ISNA(VLOOKUP(B3,revised,2,0)),projected,revised),2,0)),"",VLOOKUP(B3,IF(ISNA(VLOOKUP(B3,revised,2,0)),projected,revised),2,0))

 

[Mike H]

You weren't a million miles away, try this

=IF(ISNA(VLOOKUP(B3,Revised,2,FALSE)),IF(ISNA(VLOOKUP(B3,Projected,2,FALSE)),"",VLOOKUP(B3,Projected,2,FALSE)),VLOOKUP(B3,Revised,2,FALSE))

Mike

 

[Alicia]

Thanks that works.

You weren't a million miles away, try this

=IF(ISNA(VLOOKUP(B3,Revised,2,FALSE)),IF(ISNA(VLOOKUP(B3,Projected,2,FALSE)),"",VLOOKUP(B3,Projected,2,FALSE)),VLOOKUP(B3,Revised,2,FALSE))

Mike

 

[Don Guillett]

if your tables do not have duplicates and the value to lookup is in col C
and the values in col E.
=IF(ISNA(INDEX(E:E,MATCH(B3,C:C,0))),"",INDEX(E:E,MATCH(B3,C:C,0)))