BleepingComputer.com: Excel Formula

Hi all,

Please can you help by confirming the formula syntax below. I have a basic understanding but need clarification.

=IF(ISERROR(VLOOKUP($A5,$H$5:$N$100,4,FALSE)),0,(VLOOKUP($A5,$H$5:$N$100,4,FALSE)))

Cheers, Phil

The formula means:

Look up the value in A5 (column locked), in the table located at range H5:N100, and return the value in K#.

So, if A5 contained "Apples", and H20 contains "Apples", it will return the value in K20. If A5 contained "Apples" and H22 also contained "Apples", K20 would still be returned, as it is the first in the table.

The FALSE part of the VLOOKUP expression tells Excel to match exactly what is in A5 with the contents of the table. So, the table must contain "Apples" in the previous example to return a match. If it were TRUE, the table may be able to contain "Aples" or similar, and still return a match.

The IF statement allows the formula to provide an answer even if "Apples" isn't in the table. Normally, the formula would return the N/A! error message in this case. But by using the IF statement, we can force Excel to display a nice, tidy zero instead.

If there is anything else you wish to know, I'll try and answer further.

M

Cheers Martygeek,

it all makes sense now...much appreciated