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

JGuden

New Member
Joined
Sep 28, 2006
Messages
6
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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
Welcome to the board.

Sure, use ISNA.

e.g.
Code:
=IF(ISNA(your_VLOOKUP),formula_if_#N/A_is_returned,your_VLOOKUP) or similar
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Try this

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

JGuden

New Member
Joined
Sep 28, 2006
Messages
6
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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

New Member
Joined
Sep 28, 2006
Messages
6
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,512
Messages
5,523,323
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top