Cell P14 = 4.7
My array is H42:N72, in H42:H72 I have numbers 15,14,13....0,-1,-2,-3, etc...
In N42:N72 there is a corresponding $ amount that I would like to return based on finding the value which is greater than 4.7 (so, looking to match the $$ amount to the number 5 in column H42:H72)
I've tried using INDEX MATCH but can't figure this out - not an expert would appreciate any help figuring this out. Many Thanks.
Post a SMALL sample file that shows us what result you expect.
About 10 rows by 5 columns is plenty of data. We should not have to scroll to see/find the data! It's a SAMPLE file!
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi there,
Welcome to the Forum.
Refer workbook attached. I've created my own data table. Is this something you can work with?
If this doesn't suit your need, kindly upload a workbook with sample data as Tony suggests (after removing sensitive information)
If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.
If your requirement has been solved please mark your thread as Solved.
In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".
Kindly use [FORMULA] or [CODE] tags when posting your code.
Regards,
Sarang
Thanks Tony and Saarang - appreciate the posts. Unfortunately couldn't get your sample formula to work Saarang.
I pared down this file, apologies if still too big (I can repost), but highlighted the cells used. In summary, trying to write formula in H18 that looks first at H14, then looks for a number in column A (if there is no exact match then looking for number "less than"), when match determined returns amount from column E (the answer from book is green cell E19).
Many thanks.
Hi,
Refer workbook attached. Is this what you need?
Works great, thanks Saarang
Saarang, is there a way to tweak the formula so that if the calculation of H13/H8 is positive (say 1,000) the result returns the line that shows 8,000. Right now it looks to default to -1%. Seems to be the rounddown function but would like to have positive numbers stay above 8,000 and negative numbers below 8,000. Hopefully that makes sense. Many thanks.
1.0% 8,080 1% 40 8,120
0.0% 8,000 0% - 8,000
-1.0% 7,920 -25% (1,980) 5,940
The calculation of (H13/H8) gives you a % value (not a whole number like 1,000 as you've said) which is used inside the ROUNDDOWN function inside the MATCH. Correspondingly, the INDEX functions returns the value from the Payout column. Its not clear what you are trying to convey here.Originally Posted by tmurc123
In your example, for Feb-14, (I13/I8) gives a value of 0.0398813683984746% ~= 0.04% and so MATCH returns 5,940
If you want a default Payout of 8,000 for all %'s >= 0%, then in H18, try the below formula and copy it for all the months to the right.Formula:
Please Login or Register to view this content.
Last edited by Saarang84; 06-26-2014 at 10:41 PM.
Sorry, I realize I am not being clear. Yes the result of .04% is correct and I do want it to be a %, but 5,940 corresponds to -1% in column A. Since I13/I8 is .04% and positive, I would like it to round to 1% which would MATCH 8,120 (which rounds up to the 1% line).
If I13/I8 result was negative .04% (-04%), I'd like it to round to -1% and MATCH 5,940.
So, only a number from I13/I8 that is exactly 0% would MATCH 8,000 (this is likely to never happen but is my base) - a number that is say negative (-1.4%) would MATCH 5,096, negative (-2.1%) would MATCH 4,268, a positive (.4%) would match 8,120, a positive (1.4%) would match 8,282.
Is a better way to do this to create 2 columns that would be something like below and then create the formula to state that any number that falls between the 2 percentages returns the value in Payout? But, I would still need the positive numbers to roundup, and I guess the negatives would have to roundown. This is a commission table and so I want to make sure that if the sales level falls below 0% then the payout is less than the base of 8,000.
2.1% 3.0% 8,405
1.1% 2.0% 8,282
0.1% 1.0% 8,120
0.0% 0.0% 8,000
-0.1% -1.0% 5,940
-1.1% -2.0% 5,096
-2.1% -3.0% 4,268
-3.1% -4.0% 3,840
HOpe this makes more sense, thanks for your help and patience.
=INDEX(A8:E20,MATCH(IF(H14>0,CEILING(H14,1%),CEILING(H14,-1%)),A8:A20,0),5)
Try this
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Awesome, thank you nflsales
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks