IF statement and Vlookup together

[rfinnegan]

Hi All:

I'm trying to combine an IF statement with a Vlookup, but don't know if it is even possible.

My goal is to use vlookup to find a value in a report, but if the report wasn't completed, to then calculate a default value based on some other data.

Right now I'm running the vlookup, then when I see a #div/0 value (#div/0 is what is in the cell if the report wasn't done), I put in a second formula.

Here's my vlookup that works fine -
=VLOOKUP(A26,'Audit Scores'!C$5:H$88,5,0)

Here's the formula I use if the vlookup returns an #div/0
=IF(F6>1,0.85,0)

How can I combine them?

Thanks in advance

 

[scottylad2]

=iferror(Vlookuphere,yourformulahere) which means if your vlookup returns an error then the formula will kick in.

 

[Aladin Akyurek]

On Excel 2007 and later...

=IFERROR(VLOOKUP(A26,'Audit Scores'!C$5:H$88,5,0),IF(F6>1,0.85,0))

On all versions...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},IF(F6>1,0.85,0),
     VLOOKUP(A26,'Audit Scores'!C$5:H$88,5,0))

 

[rfinnegan]

Thanks a million. Looks like I need to upgrade to 2007.

Quick question about the first part of the equation "=LOOKUP(9.99999999999999E+307,CHOOSE({1,2}" what exactly is it doing?

Thanks again

 

[Aladin Akyurek]

Thanks a million. Looks like I need to upgrade to 2007.

You are welcome.

Quick question about the first part of the equation "=LOOKUP(9.99999999999999E+307,CHOOSE({1,2}" what exactly is it doing?

Thanks again

a) CHOOSE creates here a two-item array, consisting of the results of the two
formula expressions: the IF and VLOOKUP bits. Such an array would look like:

{0.85,45}

or

{0.85,#N/A}

or

{0,#N/A}

b) LOOKUP ignores error values like #N/A. And LOOKUP with that big number
as look up value always returns the last numeric value (if such exists) from
the two-item array (or any other reference for that matter).

From (a) and (b), we obtain the desired result as we intend.

Here some links on LOOKUP and the big number:

http://www.mrexcel.com/forum/showthread.php?t=102091
http://www.mrexcel.com/forum/showthread.php?t=310278 (post #7)