Returning a "#N/A" error with a formula

[JGuden]

Is there a way to create an embedded IF formula that the logical test is if a VLOOKUP value returned = an integer the integer is returned otherwise (in this case I get a #n/a" to return the value a new formula "0+x"? I guess I am looking for a way to have excel 2003 distinguish if the VLOOKUP value returned is an integer or not and if not, return the value of an embedded formula for the false case.

I look forward to any comments on this. Thanks.

JGuden

 

[Oaktree]

Welcome to the board.

Sure, use ISNA.

e.g.

=IF(ISNA(your_VLOOKUP),formula_if_#N/A_is_returned,your_VLOOKUP) or similar

 

[barry houdini]

Try this

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},alternative_formula,VLOOKUP_formula))

 

[JGuden]

Thank you both...I'm testing to see which works more appropraitely for the larger scheme of my sheet. Barry, can elaborate on the logic behind the "9.99999999999999E+307" section of your solution? It would me me visualize the process taking place.

 

[barry houdini]

9.99999999999999E+307 is the commonly recognised "greatest number" in Excel.

When you use

=LOOKUP(BigNum,{array})

this returns the last number in the array.

In this case the array will contain just two elements

{alt_formula_result,vlookup_result}

If the vlookup result is #N/A then the last number in the array will be alt_formula_result therefore the formula does what you want, it returns the vlookup_result, if numeric, otherwise the alt_formula_result.

Note: this only works if the results of your formulas will be numeric (or possibly #N/A)

 

[JGuden]

I want make sure I have this...

Using Lookup(bignum, {array}):

When the array vlookup result gives an N/A error BigNum requires a numeric value to be returned so by default returns the numeric value of the AltFormula as it is the only integer value?

 

[barry houdini]

That's about it.....

it's more a quirk of LOOKUP than by design, I believe

LOOKUP normally requires a sorted (ascending) reference - array or range - then the greatest value less than or equal to the lookup value will be returned, so

=LOOKUP(5,{2,4,6,8})

returns 4, the greatest value less than the lookup value (5)

If the lookup value is greater than any value in the reference then the largest value will be returned, so

=LOOKUP(10,{2,4,6,8})

returns 8

but because the reference SHOULD be sorted the largest value should also be the last, so LOOKUP returns the last - because that should be the greatest - even when the range is unsorted so

=LOOKUP(10,{2,4,8,6})

returns 6

so, finally, that means that when the lookup value is 9.99999999999999E+307 this will always be greater than all numbers in the reference and the last will be returned. Error values and text will be ignored so

=LOOKUP(9.99999999999999E+307,{100,1,"hat",#N/A})

returns 1

see Aladin's contribution here (and probably elsewhere too) for a better explanation

http://www.mrexcel.com/board2/viewtopic.php?t=105725