Is There A Better Way To Do This Formula?

  • Thread starter Thread starter Nic@Rolls-Royce
  • Start date Start date
N

Nic@Rolls-Royce

HELLO

I use a number of these formulas

=IF(ISNA(VLOOKUP(F10,QuestionData,7,FALSE)),"",VLOOKUP(F10,QuestionData,7,FALSE))

to remove the #n/a from a 'no match found' vlookup

Is there a more lean way of doing this, which may save some calc time
on a oldish computer??


Thankx in advance

Nic
 
Hi Nic
If you want to prevent these kind of error there is no other way
(AFAIK). If you just want to prevent the display of error you can try
conditional format and choose a white font for #NA errors. But this
will affect just the display. So if you're using the VLOOKUP results in
formulas you'll have to use the formula below.

HTH
Frank
 
Nic,

AFAIK you can't really get the error trapping without the process cycles.

You could try

=IF(ISERROR(MATCH(F10,H1:H100,0)),"",VLOOKUP(F10,QuestionData,7,FALSE))

where H1:H100 is the first column of QuesionData, but this may not give any
noticeable improvement. If the speed is im portant, take a look at
http://www.decisionmodels.com/calcsecrets.htm for a better understanding of
how to optimise the spreadsheet.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Separate the VLOOKUP part into a different cell.
Then test that cell for N/A:
=IF(ISNA(other_cell),"",other_cell)
This will make it twice as fast.
 
There are alternatives with better performance though:

http://makeashorterlink.com/?H28622847

See also the reply by Stephen Bye in this very thread.

Turning back to a proposal of mine, made a few years ago:

This #N/A issue will never cease to be an issue. I've seen arguments for
keeping #N/A's in order to avoid computing the same thing twice. Given this
stance, one has to accept the cost of processing an #N/A-ridden range: SumIf
instead of Sum and of course array-formulas to compute an average or some
other statistics, and so forth. Not very appealing performancewise. What
would really help is to enhance the native syntax of VLOOKUP, HLOOKUP, and
even MATCH...

VLOOKUP(LVal,LTable,ColIdx,MatchType,ReturnVal)

where ReturnVal is optional. If ReturnVal={}, then #N/A is deafult,
otherwise the function should return ReturnVal as specified (e.g., 0 or "")
upon failure.
 
Then why not just write a UDF with the extra parameter to do the VLOOKUP and
conditionally return the result? Would the UDF overhead outweigh any
performance gain?
 
Tests (by means of UDF) show that the existing spreadsheets won't breakdown
by extending built-in VLOOKUP, etc. with an optional ReturnVal.

Tests also show that a UDF incorporating VLOOKUP and allowing for a
user-specified ReturnVal is rather slow, thus not a good solution compared
to the known efficient solutions.
 
Hi Nic,

I use the OR() function to check to see if the lookup
value is there first before doing vlookup() as follows:

=IF(OR(F10=QuestionDataFirstColumn),
VLOOKUP(F10,QuestionData,7,FALSE)),"")

then CTRL-SHIFT-ENTER since OR() is being used as an array
function.

I have taken this approach for the past five years on
large models. However, I cannot comment on the
performance of this approach as compared to others
recommended.

Kem
NYC
 
Back
Top