VLOOKUP returns #VALUE!

[JoeKing]

Is it possible to have VLOOKUP substitute the returned #VALUE! with a custom value, say a text string that says 'Not Available'

Basically I have a pricing tool that I perform a VLOOKUP on and if there is no price against an item, the result is #VALUE! but I want this to be the defined text I choose. Perhaps to complicate matters, the cells are formatted as Accounting with 2 decimal places, because these are prices.

Is this possible?

 

[rallcorn]

Edit formula
=if(iserror(your formula),"Not Available",(your formula)

if you have Excel 2007 use
=iferror(your formula),"Not Available")

 

[JoeKing]

Edit formula
=if(iserror(your formula),"Not Available",(your formula)

if you have Excel 2007 use
=iferror(your formula),"Not Available")

Thanks, what am I doing wrong please...

=IF(ISERROR(VLOOKUP(A1,'Sheet1'!$A$2:$C$1566,3),"Not Available"),((VLOOKUP(A1,'Sheet1'!$A$2:$C$1566,3)/$B$4*(1+$B$5)/(1-$B$6))))

 

[rallcorn]

try this:
=IF(ISERROR(VLOOKUP(A1030,'Non Warranty Repair'!$A$2:$C$1566,3,false)),"Not Available",(VLOOKUP(A1030,'Non Warranty Repair'!$A$2:$C$1566,3)/$B$4*(1+$B$5)/(1-$B$6))

 

[Aladin Akyurek]

Try...

If on Excel 2007 or later:

=IFERROR(VLOOKUP(A1,Sheet1!$A$2:$C$1566,3)/$B$4*(1+$B$5)/(1-$B$6),"Not Available")

Otherwise:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,VLOOKUP(A1,Sheet1!$A$2:$C$1566,3)/$B$4*(1+$B$5)/(1-$B$6)))

If Sheet1!$A$2:$A$1566 is not in ascending order, change:

VLOOKUP(A1,Sheet1!$A$2:$C$1566,3)

to:

VLOOKUP(A1,Sheet1!$A$2:$C$1566,3,0)

Custom format the cell of the latter as:

[=0]"Not Available";General

 

[JoeKing]

This seems to be working, so I guess a big thank you to you Aladin

Could you explain how it works, I am using Excel 2003 for my sins.
What does the =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0, part do and why the big number?

 

[Aladin Akyurek]

This seems to be working, so I guess a big thank you to you Aladin

Could you explain how it works, I am using Excel 2003 for my sins.
What does the =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0, part do and why the big number?

You are welcome.

CHOOSE creates here a two-item set (array) like:

{0,2.4}

or

{0,#VALUE!}

LOOKUP with the big number (an Excel constant to be found under Excel's limits) will always return the last numeric value from such a set: 2.4 from the first set, 0 from the second.

http://www.mrexcel.com/forum/showthread.php?t=310278,

post #7, explains how LOOKUP works.