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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,015
Office Version
  1. 365
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
 

Forum statistics

Threads
1,141,227
Messages
5,705,142
Members
421,380
Latest member
Nuwan Sanjeewa Aponso

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
Top