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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Welcome to the board.

Sure, use ISNA.

e.g.
Code:
=IF(ISNA(your_VLOOKUP),formula_if_#N/A_is_returned,your_VLOOKUP) or similar
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,046
Messages
6,105,590
Members
447,972
Latest member
carrieann

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top