Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
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.

Photo

Excel Formula


  • Please log in to reply
2 replies to this topic

#1 learningIT

learningIT

  • Members
  • 3 posts
  • OFFLINE
  •  
  • Local time:06:08 AM

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

BC AdBot (Login to Remove)

 


#2 MartyGeek

MartyGeek

  • Members
  • 27 posts
  • OFFLINE
  •  
  • Local time:06:08 AM

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

learningIT
  • Topic Starter

  • Members
  • 3 posts
  • OFFLINE
  •  
  • Local time:06:08 AM

Posted 17 July 2005 - 04:11 PM

Cheers Martygeek,

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users