IF(ISNA....

[Guest]

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!

 

[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

 

[Guest]

Thank you! Works like a charm.

 

[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

 

[Guest]

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.

 

[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