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

#### JGuden

##### New Member
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
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
Try this

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

#### JGuden

##### New Member
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

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
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

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

Replies
6
Views
214
Replies
6
Views
152
Replies
6
Views
180
Replies
1
Views
153
Replies
3
Views
159

### Forum statistics

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.

### Which adblocker are you using?    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

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