{ "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" }

How can I index match a relative value? I 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 My formula is currently =index(D26:D48,match(B56,B26:B48,1)

Post Photo
like
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

like

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.

like

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.

Additional Posts

3.00 UG gpa (death of a parent, caretaker of the other), 3.95 master GPA both in engineering, 730 GMAT. Have killer ECs, decent WE but not gold. I'm applying to 10 of the T15 in R1, with Cornell as my safety. Should I add more schools that are lower ranked in or just see where I land after R1? Really want to have at least 1 acceptance after R1.

like

Ladies I am 35 weeks pregnant and going on maternity leave in 2-3 days. Politics on my project has led to poor project snapshots. What should I do?

like

Anyone watching Indian Matchmaking? Aparna and her mother have to be the worst, right? She thinks she's a godsend and quite frankly she looks like a potato, is ignorant, doesn't sound nearly as intelligent as she professes to be, and is even more negative than I am! That's gotta say something.

likefunny

Dropping my entire 401k in ETH if it dips below 3k again.

likeupliftingsmarthelpfulfunny

Anyone know how product managers are positioned at Apple? I haven’t heard too much about PMs there compared to at other FAANG. Is it “true” product management, agile, or more like project managers?

like

Looking for a strong resource to join a tiger team focused on managing and supporting large scale pursuits and partnerships in the provider space ($1B+ deals) at Optum. The role isn’t consulting but aligned very closely and hyper focused on closing very robust diligence efforts that are similar to a diagnostic assessment. Preferred big 4 experience that loves PMO and due diligence with a smile. 2nd year BA or 1st year C). DM me if interested.

like

Switch to Big 4 Corp Dev from Big 4 M&A Strat Consulting or continue M&A Strat Consulting path w/ MBA or PE/IB in mind?

likehelpful

In terms of better compensation in technology consulting is it deloite>pwc>ey>kpmg ?

like

Poll for the group - How common are PIPs at the various Big 4? Is it like the whole bottom 10% of everyone’s group or a once-in-a-while-this-person-is-dumb kind of thing? Have heard of quite a few within my group and wondering if it’s normal or something isn’t quite right.

like

Looking into cashing out AmEx points. Tell me what to do!

like

Any suggestions on good cordless vacuum cleaners for hardwood as well as carpet..With work from home life.. home is becoming dirty sooner!

like

One of my employees is the only one in the firm who knows how to use a critical piece of outdated software. I overheard them speaking about accepting a job interview. Should I get them to train another employee?

like

Local client, what’s the expense policy? Can I still expense tolls?

like

Had an intern whom I could not recommend for hire. She didn’t report to me directly so I never gave her feedback directly. She just asked if she can list me as a reference How would you respond?

like

How long copr of parent and grand parents will be valid ?

like

Does VP level mean adding profit sharing to your compensation?

likefunny

Leaving Deloitte for a better consulting firm & a 50%+ raise. Thank you to D, if they didn’t pay me raises 1/10th the size of competitors, I would’ve never applied!

likesmart

if you can choose: 1. audit with definitely (know firsthand from a close friend) amazing company culture - mid market (overtime not encouraged, if you do need to do overtime you get hourly pay for it or can choose to exchange it as holiday, can potentially do secondment to FDD in the future but not guaranteed in terms of timeline 2. FDD with potentially shit company culture (partner scream at you for not rec ing excel sheets properly, sexist comments made - but all these info was from contd’

like

Interviewing @ FAANG, do you reference actual client names in your interviews?

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
Download Fishbowl to see what others are saying
That was just a preview…
Sign Up to see all discussions
  • 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
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the Fishbowl app

Get realtime notifications and see
what's happening in your industry
from the palm of your hand.

For account settings, visit Fishbowl on Desktop Browser or

General

Legal