Can someone tell me what this formula means?

T

Titanus

I'm trying to decipher a progam but don't know what this is saying:

=IF(ISERROR(VLOOKUP($C17,'6'!$B$1:$E$25,4,0))=TRUE,"",VLOOKUP($C17,'6'!
$B$1:$E$25,4,0))

I understand that it's doing a VLOOKUP of what's in cell $C17, but the
'6' part attached to the table is throwing me off. Also, does the
=TRUE thing mean I'm NOT looking for an exact match?

Can anyone put this into English for me?
 
D

Dave Peterson

6 is the name of the worksheet that contains the table in B1:E25.

if(iserror()=true
checks to see if there is a match in the first column (B1:B25). If the formula
returns an error, then iserror() will be true and the next portion says to show
nothing ("") in that cell.

It's a way of avoiding the #n/a errors to make the worksheet look pretty.
 
D

David Biddulph

The '6' before the ! sign is presumably the name of the worksheet in which
the $B$1:$E$25 lookup range resides.
The =TRUE looks unnecessary to me, as I would expect ISERROR() or
ISERROR()=TRUE to give the same result, i.e. TRUE if ISERROR() is TRUE, and
FALSE otherwise. It seems to be returning an empty string "" if the
VLOOKUP() reurns an error condition, but otherwise will return the results
of the LOOKUP(). Because the last parameter of the VLOOKUP() is zero (or
FALSE) then if it doesn't find an exact match it will return #N/A and
trigger your error condition to return a blank from the IF() statement. If
the last parameter of the VLOOKUP() had been TRUE, then if it couldn't find
an exact match it would have returned the largest value that is less than or
equal to your lookup_value $C17.

Each of the functions in Excel (except DATEDIF) has an entry in Excel's help
facility, and gives examples to help to understand the syntax.
 
Top