Same function two times in one formula?

[schlesinger]

Can anyone explain how Excel treats function calls like:

=IF(ISNA(VLOOKUP(100,MyRange,2,False)),"",VLOOKUP(100,MyRange,2,False))

A colleague says that Excel "intelligently" figures out that the two vlookup calls are the same, so it does only one vlookup call in order to return the result of this formula (instead of calling vlookup twice). Is this true? And what are the limitations of this call handling?

I know I should use IFERROR(vlookup..) here. But what if I have an outside function like BDP(...) which returns the _text_ "#N/A N A" (and isserr doesn't work)? Should I make a User-defined function to avoid making the call twice?

Thanks!

[MarvinP]

Hi schlesinger,

The IfError was introduced in 2007 Excel. Your way of doing it is how we had to do it back in 2003 and earlier Excel.

I wonder how your freind knows how Excel deals with your formula. Find the Evaluate feature and see how Excel really does deal with it. I'd be thinking it would need to do the VLookup twice if it wasn't an error on the first try.

[scottylad2]

IF(ISNA(VLOOKUP(100,MyRange,2,False)),"",VLOOKUP(100,MyRange,2,False))

You can break this formula down quite easy to state what it does, it says if the first Vlookup returns #NA then your answer will be left blank, in order for that to happen excel has to do the first Vlookup and if #NA is the result then there's no need for it to do the second. if there's no #NA then Vlookup number 2 will be done, the first one is only testing for #NA

you could change the if to reflect you want tha #NA displayed, which would work only on that error but you could have a not available in either your lookup cell or your lookup table. you could use

=IF(ISNA(VLOOKUP(100,MyRange,2,False)),"#NA",VLOOKUP(100,MyRange,2,False))

[schlesinger]

hey marvin, thanks, i checked out the evaluate formula. you are right excel does seem to evaluate the vlookups separately, going as far as to evaluate individual references separately. (like if it was vlookup(a5, myrange, 0) it would evaluate the a5 twice, not once.) it seems inefficient.

scottylad2, i appreciate your help but my question was asking whether excel treats the vlookup as two separate calls or as one call, not what the example does--i understand what the example does, obviously.

[scottylad2]

scottylad2, i appreciate your help but my question was asking whether excel treats the vlookup as two separate calls or as one call, not what the example does--i understand what the example does, obviously.

Wrapped up in the If, it won't do the vlookup twice per se. It will evaluate the first part, and if it returns TRUE then there would be no reason to do the second one. If False then the second Vlookup would be evaluated on its own content with the first one being ignored. Obviously you knew that though

[martindwilson]

it probably doesn't matter as long as you don't use hundreds of vlookups
but read here
http://www.decisionmodels.com/optspeede.htm
see the section
Unsorted Data with Missing Values.