LinkBack URL
About LinkBacks
Hi All,
I need a formula to return the closet value when i don't have an exact match
Sample sheet attached
Any help greatly appreciated
Kevin
Hi All,
I need a formula to return the closet value when i don't have an exact match
Sample sheet attached
Any help greatly appreciated
Kevin
Last edited by AliGW; 12-21-2016 at 01:36 PM.
From your file:
Why? In any case, based on your lookup table, you can't. What is the logic for doing so?I would like 3999 and 4001 entered in F3 to both return 700
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Your example looks wrong. I assume you meant 800.
Try this Userdefned Function
This formula will then return 800
Please Login or Register to view this content.
Formula:
Please Login or Register to view this content.
Last edited by mehmetcik; 12-21-2016 at 01:09 PM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
We cannot change the basic search algorithms built into Excel's lookup functions. When we want a different search algorithm other than those pre-programmed for us, we must come up with our own search algorithm. In this case, I am failing to understand how you are choosing to return 700 for both 3999 and 4001. 3999 will return 700 under the usual binary algorithm (3999 will match with 3500, since it is between 3500 and 4000 in the lookup table, which then returns 700). But I do not understand why 4001 should match with 3500 (and thus return 700) and not with 4000 (and return 800 like it does).
Is it possible that you mistyped your example? Did you intend for 3999 and 4001 to both return 800 (both match to 4000)? If that is the case, I would add a new column to my lookup table, that will compute the actual divisions that I want to use for the lookup values. Assuming I understand what "closest" means, I would expect to have lookup values of 500, 750, 1250, 1750,... (essentially the midpoints of the current divisions). Search the table based on this helper column.
Originally Posted by shg
Yes sorry i meant 800
What is the tipping point between 3500 and 4000?
I am using 3750
Your example looks wrong. I assume you meant 800.
Try this Userdefned Function
This formula will then return 800Please Login or Register to view this content.
Formula:
Formula:Please Login or Register to view this content.
You can put this array* formula in E3:
=IF(COUNTIF(B3:B18,F3),VLOOKUP(F3,B3:C18,2,0),INDEX(C3:C18,IFERROR(MATCH(F3-MIN(ABS(B3:B18-F3)),B3:B18,0),MATCH(F3+MIN(ABS(B3:B18-F3)),B3:B18,0))))
*NOTE: an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual < Enter >.
Any value in F3 between 3251 and 3750 (inclusive) will return 700, values between 3751 and 4250 will return 800, and so on.
Another (simpler) way, as you have increments of 500 between the values in column B, is to use this non-array version:
=IF(COUNTIF(B3:B18,F3),VLOOKUP(F3,B3:C18,2,0),VLOOKUP(F3+250,B3:C18,2,1))
though this will match between 3250 and 3749 (you can adjust the 250 in red to affect this behaviour).
Hope this helps.
Pete
Thank you all for your help, I used Pete's array suggestion
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Because of the regularity of your data, you can also use this formula to give the same results as the array formula:
=MROUND(F3-1,500)*0.2
If you want the equivalent of the second formula that I gave you, just remove the -1.
Hope this helps.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks