Need Help Deciphering this function

[TheBird]

Any help deciphering this would be greatly appreciated! I see an error function but the 2 vlookups are what i'm struggling with.

=IF(ISERROR(VLOOKUP($H12,dusa,4,FALSE)=TRUE),0,VLOOKUP($H12,dusa,4,FALSE))

 

[Matty]

but as I mentioned previously, it's simply not needed.

The =TRUE bit obviously!

Matty

 

[T. Valko]

Hi Biff,

I guess so, point it out to me please.

=TRUE????

Ak

You reproduced it:

=IF(ISERROR(VLOOKUP($H12,Dusa,4,FALSE)=TRUE),0,VLOOKUP($H12,Dusa,4,FALSE))

Let's assume the result of the lookup is X.

As written, that formula is doing this:

=IF(ISERROR("X"=TRUE),0,VLOOKUP($H12,Dusa,4,FALSE))

It works but it's just by "dumb luck".

If the lookup returns #N/A then:

=IF(ISERROR(#N/A=TRUE),0,VLOOKUP($H12,dusa,4,FALSE))

ISERROR(#N/A=TRUE) = ISERROR(#N/A) = TRUE

And the result of the formula will be 0 as expected.

 

[T. Valko]

If VLOOKUP is supposed to return a number...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,VLOOKUP($H12,dusa,4,0)))

would probably be a tad faster.

This will do the same thing and is a tad cleaner:

=LOOKUP(1E100,CHOOSE({1,2},0,VLOOKUP($H12,dusa,4,0)))

 

[T. Valko]

This will do the same thing and is a tad cleaner:

=LOOKUP(1E100,CHOOSE({1,2},0,VLOOKUP($H12,dusa,4,0)))

And if they're using Excel 2007 or later this would be even better:

=IFERROR(VLOOKUP($H12,dusa,4,0),0)

 

[Matty]

Yet another option (if not on Excel 2007):

=IF(ISERROR(MATCH($H12,lookup_array,0)),0,VLOOKUP($H12,table_array,4,0))

Which may be a tad faster or slower than the other options... who knows!

Matty

 

[T. Valko]

Yet another option (if not on Excel 2007):

=IF(ISERROR(MATCH($H12,lookup_array,0)),0,VLOOKUP($H12,table_array,4,0))

Which may be a tad faster or slower than the other options... who knows!

Matty

Do you want to know?

There is calculation timer code here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

 

[Matty]

Do you want to know?

There is calculation timer code here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

Thanks Biff.

Matty

 

[T. Valko]

Thanks Biff.

Matty

You're welcome!