Excel Formula

BleepingComputer.com: Excel Formula

Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.

Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Page 1 of 1

You cannot start a new topic
You cannot reply to this topic

Excel Formula Explanation of the following formula

#1 learningIT

New Member

Group: Members
Posts: 3
Joined: 12-July 05

Posted 12 July 2005 - 11:02 AM

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

#2 MartyGeek

Member

Group: Members
Posts: 27
Joined: 16-July 05

Posted 17 July 2005 - 04:13 AM

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 :thumbsup:

#3 learningIT

New Member

Group: Members
Posts: 3
Joined: 12-July 05

Posted 17 July 2005 - 04:11 PM

Cheers Martygeek,

it all makes sense now...much appreciated :thumbsup:

BleepingComputer.com: Excel Formula