{ "media_type": "text", "post_content": "How can I index match a relative value? \n\nI want to look up the per unit cost of 280M units, but don’t have an exact match. The numbers in the picture below are actual units/infra I’m searching against \n\nMy formula is currently =index(D26:D48,match(B56,B26:B48,1)", "post_id": "60ff0244384e940020712e76", "reply_count": 7, "vote_count": 4, "bowl_id": "5a56f68d15f6ef0013aad256", "bowl_name": "Excel Genius" }
Posting as :
works at
You are currently posting as works at

First of all, you don't want to use anything besides 0 on the match function except in very specific cases - and you need to know how match looks for values. But the closest match is actually a straightforward formula using abs, min and index match. Check the link: https://www.excel-easy.com/examples/closest-match.html

THANK YOU! so helpful for this and other projects :)

I don’t know what you’re trying to determine, but you’ve got some pretty poorly behaved data. It seems like a regression on scrubbed data would make more sense. Maybe identify another data item which would give you a better correlation.

I’m trying to determine infrastructure costs for a saas application. We are moving from on-prem to cloud. - this will inform the added price per unit given added infra costs. The units (along with the type of database used) are key inputs to the infrastructure costs. We do business in terms of units sold, so deviating could be a challenge

Sort the table from low to high on units, then use a vlookup with approximate match (set the last argument to FALSE)

Your index formula appears to capture only column D

Are you assuming that the per unit cost for 280k units would be between the 240-414k costs, or are you assuming it’s non-linear (i.e. anything below 414k threshold uses 240k pricing)? The answer isn’t the same for both.

### New to Fishbowl?

unlock all discussions on Fishbowl.
That was just a preview…
• Discover what it’s like to work at companies from real professionals
• Get candid advice from people in your field in a safe space
• Chat and network with other professionals in your field

Share

#### Embed this post

Copy and paste embed code on your site

Preview