I'm supposed to use a VLOOKUP function to show the cost of yardwork on a small, medium, large and commercial sized lawns. There is a table in another sheet that shows these costs. I can't get the VLOOKUP to work, I got it to work with a nested IF function but I'm supposed to use VLOOKUP. When I try to show the total cost with discounts, using the HLOOKUP from another table, I only get "VALUE" errors.
Thanks!
Try this...
=INDEX('Pricing Guide-Updated April 7'!$D$3:$G$7,MATCH('Customers-April 7-11'!E4,'Pricing Guide-Updated April 7'!$D$3:$D$7,0),MATCH(H4,'Pricing Guide-Updated April 7'!$D$3:$G$3,1))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Is there no way to do it with VLOOKUP?
Yes there is a way, but FDibbins as given you an alternative to the Hlookup to return a discount value.
As for the vlookup try this...
=VLOOKUP(E4,'Pricing Guide-Updated April 7'!$A$4:$B$7,2,FALSE)
Have I made you happy ??? If yes, please make me happy by pressing theAdd Reputation button in my post.
Please don't forget to do the same to other contributors of this forum.
ThanksI don't void confusion, I create it
I know there's different ways to do formulas, but unfortunately I have to use the formulas that we are learning that particular week
Why do I need the "FALSE" at the end? I thought it was optional?
[range_lookup]: Do you want an exact match? Is an approximate match okay?
If you use TRUE as the last argument, or omit the last argument, an approximate match can be returned. This example has FALSE as the last argument, so if the product code is not found, the result will be #N/A. (Note: Excel is rather forgiving, and will accept 0 instead of FALSE, and 1 instead of TRUE.)
Source of explanation
Thanks ratcat. So If I want to use HLOOKUP to find the Total with Discount, do I need to use a combination of HLOOKUP and INDEX/MATCH?
I'm assuming I'll use HLOOKUP to determine the discount percent and then multiply that by the subtotal?
If this is homework, it is against forum policy to do your homework for youWe can advise on what you have already tried.
To answer your question regarding the FALSE, yes it is optional, but its good practice to specify whether the data is sorted (TRUE) or not (FALSE) - FALSE will look for an exact match, TRUE will return the 1st value smaller than what you are searching for, if no exact match is found
Dibbins, when I use HLOOKUP, and when I tried your INDEX/MATCH function for the Total with Discounts, I get values of 0$, $.02, $.03, which doesn't make any sense because shouldn't it return the % value of the discount, and then I just add a *(H4) <---(Subtotal) to get the total final price?
That just means you need to format the answer to % - or apply that value to the amount owed
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks