IF(ISNA....

O

olrustyxlsuser

I understand that to remove the #N/A's from cells in my lookup I need to use
the IF(ISNA function. Could someone help me out by correctly adding this
function to my lookup formula.
=VLOOKUP($A$17,$D$37:$DV$47,COLUMN(B16)-1,FALSE)
Also, I have a question in a different thread about copying the
formats/formulas from a lookup, using VB. Will this new formula be affected
by the VB?

Thanks for your answers!
 
P

Pete_UK

Try this:

=IF(ISNA(VLOOKUP($A$17,$D$37:$DV$47,COLUMN(B16)-1,FALSE)),"",VLOOKUP($A
$17,$D$37:$DV$47,COLUMN(B16)-1,FALSE))

All one formula - beware of spurious line breaks.

Hope this helps.

Pete
 
B

Bernard Liengme

As an alternative to Pete_UK's method, try
=IF(countif($D$37:$D$47,$A$17),VLOOKUP($A$17,$D$37:$DV$47,COLUMN(B16)-1,FALSE),
"")
Just a little shorter
best wishes
 
O

olrustyxlsuser

Bernard,
Thank you. Your formula works very well also!
If anyone is comfortable in VBA, you might be able to help me with my
question posted as "copying formats from a vlookup" in the Excel programming
section.
 
R

Rugdoody

Rusty -

Did you get an answer for your vlookup formats question? I have searched
for it and can't find the post - and am trying to do something similar i
believe. trying to have the format lookup as well as the value. Thanks for
any help!!!

Doug
 
Top