I am probably over complicating this which is why I can’t figure out the best formula, but I am trying to return a set value (column D) if a date falls within the date range from columns A and B. Basically I want to be able to type in any date and if that date falls within one of the ranges to give me only the value for that row from column D. I thought an IF(AND) nested in VLOOKUP would work, but I can’t get it to work. Any advice?

Post Photo
like
Posting as :
works at
You are currently posting as works at
Highlighted IconHIGHLIGHTED

So there are three scenarios: either you have no date that falls between those, you have one date or you have more than one date.

If you only want to find one match (i.e. the first match), you can use index match

If you want to find all matches, you need =UNIQUE(FILTER())

For index match, the logic would be:
=INDEX(Column D,MATCH(1,(start_date>=target_start)*(end_date<=target_start),0))

For unique filter, the logic would be:
=UNIQUE(FILTER(column D,(start_date>=target_start)*(end_date<=target_start)))

likesmart

I use these functions all the time on smaller data sets, but when you have 200k rows, it'll take 2 hours for it to calculate

I would spread the formula across more columns. You have the space and it makes it easier to find which bit is broken. So leave column D.
Then obviously have a cell where you can type your date that you want the formula to reference. Maybe on a different sheet or whatever. I’ll refer to this as Date Cell
Then in column E (starting in row 2) have if(and(datecell>a2,datecell<b2),d2,””).
Take note of whether you want the date to include the start date or end date or be between them. If so, you greater than or equal to above rather than just greater than/less than etc.

like

Could that be because the data does not have and rows which cover those dates? Like, there is nothing to return?

Recent IconRecent

XLOOKUP(date, column B, column D,,1) should do the trick. The 1 will set the lookup to look for an exact match or the next largest item

likesmart

=sumifs would probably work

like

If you can add a column to your data set, maybe this can help.

Post Photo
like

Between function inside If statement should work

How are you calculating column d?

I think I need to see a picture of what you’re trying to pull after your logic statement t

I think everyone’s over complicating it. You just need to convert your list into one set of dates(rather than 2) assuming there are no overlaps and use the Lookup formula - lookup array = dates, return array = comp rate, lookup vector = date input. Great formula for looking up values between buckets

Okay, so can you explain to me how would you:

1 - in one column (as date) have enough information to both make sure the initial date is higher than a given criteria, and the end date is lower that another given criteria?
(It's not about making an IF or equivalent, as this would make it pointless since you can do it faster, both syntax-wise and performance-wise inside the formula)

2 - explain how, by creating a "date bucket", that would make it impossible to have more than one match?

Genuinely curious

1 - I’ll use date inputs as the example as that’s what is relevant here. So the way the lookup formula works is to lookup a value (input date - the author suggested they wanted to type one date and get the max comp rate), and returns the corresponding value of the date input depending on which range it sits in. So if you had a list of the following dates (excuse my date formats, I’m Australian) in column a, and (values) in column b - 1/1/2022 (1000), 1/2/2022 (2000), 1/3/2022 (3000), and you typed an input date of 10/2/2022 - you would return a value of 2000, which in this time sample would be the max comp rate for that date.

The author has an ‘end date’ which isn’t needed as it is just the last day of the period / the day before the next period, which the formula can automatically distinguish.

2 - Given the date ranges are all one year in the authors photo, there can not be more than one match for the input date (it either sits in this year’s period, or another) - if there were overlaps of the date ranges then you couldn’t use this formula but that wouldn’t make much sense in the authors lookup table.

See below photos. Let me know if I missed anything

@author yeah I’ve had this problem in the past, sounds like a formatting issue, very annoying. Have you tried just creating the table yourself? In column A start with 10/1/1991 in cell a2, then plus one year onto the above cell going down (can use EDATE formula to +1 year). This way you will know it’s a date format at least. Then bring in the corresponding max comp rate by either using qn xlookup or just reversing the order in the original table and copying across.

It may also be because your lookup table dates are descending - try going from 1991 downwards to 2023 rather than the other way around as the above approach uses.

Formula

Post Photo

I would try a =filter() formula

I do an index match formula if dates don't overlap. Like

=INDEX({outcome column},MATCH(1,({start dt column}>={dt})*({end dt column}<={dt}),0))

CTRL+SHIFT+ENTER

done

Be warned though, index formula is an array formula so if you are using this on a pretty large data set, you can expect your Excel to be extremely slow. I'd limit the column reference to just the lookup array by locking the cell reference.

Extremely handy and accurate formula

Related Posts

Are there any tech companies that match MBB analyst comp for non-technical roles (BizOps, strategy, etc)? 3 YOE analyst 250k - 300k all in from MBB in 22 (depending on rating) but seems like the only way to stay in the same range with limited YOE is PE/VC?

funnylike

Looking for 2 account directors 7-10 years of experience in Chicago to lead business and grow it. The salary is $130k. It’s a multicultural agency with big accounts including fast food and CPG.

like

Hi Fishes,

Can you please help understand my in hand salary based on below break-up?

YoE: 4years & 10months

Post Photo
like

What’s an average salary for a mid-level post producer in LA /NYC? Does anyone know of a post house looking to hire a kick ass, scrappy post producer? Thinking about switching over from agency side🙃

like

Can anyone help with the avg pay for Sr. Program Manager (Non Tech) with Amazon in London?

Comparative view:
India (Blr) - 46 LPA + ESOP

Amazon- 80K GBP (Base + Bonus)

What makes more sense?

like

At Visa Inc, is it standard to have a sign on bonus with package?

like

How’s WLB, Benefits, and Compensation for ISR/Account Manager at VMware?

like

I am contemplating a new offer for Validation Lead role (non- managerial) at a Big pharma, with 4 years experience -post masters degree. Suggestions on what the compensation should look like ?

like

Hi Fishes,
I have cleared interview in
AArete . Need help on what is the salary i should ask in the HR round.
YOE - 12
Role - Manager
Current CTC - 49LPA (Onsite salary)

Capgemini - 32LPA with C2 band
Arete - HR discussion pending (I was really happy with people how they dealt with interview process)
Quest - 33LPA

I am new to this platform, need 11likes to message someone directly.
Thanks in advance.

like

Hello, I am hiring a General Manager - preferable with a local passport and education - for anm digital advertising agency to start up subsidiary in SG to cater for the local SEA market.
What should I expect this person would require to receive in salary and benefits?

like

Does anyone know what the hourly rate is at Novant in the Charlotte area is with 20 years experience?

like

Is there an opportunity for Cloud Solution Architect in Google as an IAAS part and what would be the salary can someone expect?

I am currently working in Microsoft as a Partner Technical Consultant on IAAS from last 4 yrs with 10+ yrs of experience

like

Hello fish and sharks! I am an analyst at a pharma company in NYC. I’m trying to pivot to consulting specifically in Risk Management. My boss and I talked about consulting firms and he said how consultants work all the time outside of working hours.. is it really that bad? And is the compensation not great compared to how much you put in?

like

Guys .. has salary credited for you?

like

I had given interview last week for Capgemini and had salary discussion on Monday. They told me they need approval and will come back. Within how many days can I expect offer letter?

like

How much does a newly promoted Senior Manager in Audit make in HCOL - San Francisco/New York ? Is $150k reasonable or is the range higher?

like

At what salary should I buy my first luxury watch?

like

Any joining bonus will we get if we join in tata elxsi as experienced?

Hello All!

I am a graduate of Suffolk University, and I recently received my masters in Business Analytics. I am currently pursuing a career as an IT Business Analyst at MSU Denver, which I have the 30min first round interview scheduled for Tuesday, November 29th.

If anyone is or was previously employed as an analyst at MSU Denver, would you mind providing some insight into the culture, the interview process, salary information, etc. I would truly appreciate any help as this is a role I want

like

I got sleected in Hexaware 21.5lpa+1l as joinjng bonus+1l as retention bonus, TCS 21.5lpa, EY gds18lpa1l as joining bonus. I have 7.5 years of experienceWipro EY Hexaware Technologies . Kindly help me to choose 1 for job security,wlb and good hikes. Is it good to join atos for fedex project.

More Posts

Dumb question but heard this from someone wanting to confirm (feels untrue) but if Deloitte sponsors you do you not have to take the GMAT/GRE?

like

Anyone wants to ride in park slope area? Can go to red hook or greenpoint

Can the fact I’m pretty bad with spelling (considering my education level) be related to my ADD?...or I just need to shut up and go read more often?

Merry Christmas! 🎄

Post Photo
like

What is the CTC offered by Capgemini generally for C2 level .?

like

I’m curious what people are making as BDMs in Automotive. I’m personally in the automotive industry responsible for finding new business opportunities in the semiconductor realm.

like

Can someone walk me through the Do's and Don't's of AA miles? I think they're a joke.

I have 65k miles and I could only pay for a one-way ticket from PHX-JFK. I had to book 1 leg with miles and the 1 with cash, but booking the other leg in cash was about 80% of the round trip in cash.

My mom has 12k miles that expire soon, transferring them would cost me $450. I can pay for a round trip with that cash but I can only pay for a trip to my kitchen with those 12k miles.

What am I doing wrong? 😂

like

Anyone have a recommendation on a good portable monitor to connect to my laptop? Preferably no power cord required and a decent resolution to use with spreadsheets, pptx etc. Thanks!

like

Currently a Wealth Advisor for Citi but I honestly hate it here with a passion it’s the worst company I’ve ever worked for in my life and I’ve been in Wealth Management in the banking channel for over a decade. Series 7, Series 63, Life, CFP, ChFC, and soon to have my MBA with a focus in Mergers & Acquisitions. I’m look to enter another area of Finance at this point instead of starting over somewhere else from scratch, but not sure what direction to go. Interested in Investment Banking or M&A.

It’s funny - I’ve been rejected so many times in my short career. But when it happens, I still feel a small sting. Just got dinged from an interesting role - how do you deal with the aftermath?

Anyone familiar with Yext and their DC (Rosslyn) office?

like

Hello! Anybody here who works at Houston Methodist Sugar Land, TX? How's the work environment there? Just got an offer and I'm supposed to start this July. Also, is $50 base pay enough for a family of 4 in Sugar Land? We're relocating from another state and I have no idea what the cost of living is in that area 🤷🏽‍♂️

like

I self medicated anxiety and PTSD for years with alcohol, but recently completely stopped. I am on medication to help anxiety but I struggle constantly to focus and my work suffers. Any tips?

like

Hey so like - newfound reason for my procrastination since I got on Ritalin - a tendency towards "putting things off so I can enjoy the hyperfocus later". When I just started taking the meds I was super effective for a few weeks but now my brain seems to be figuring out ways around the drugs 🤦🏻‍♀️ any ideas on how to not fall back into procrastination behaviours?

like

Has anyone heard of or know anything about Transamerica, Inc.? Trying to do my research on the company

like

Anyone heard of “Junior League of Chicago?” Worth it? Looking for something to get more involved in.

like

Dorian is making landfall soon and all I can actually think about is - huh, will there be a hurricane relief credit for this year bc of it? 🤦🏻‍♀️ what is wrong with me

likefunnyhelpful

I have an extremely difficult 2.5 year old boy. I know this is a difficult toddler age altogether but I think I’m experiencing way worse. There are frequent tantrums in public settings and it’s very embarrassing. We can’t do anything. He’s in daycare and apparently he thrives, so we just don’t get it. Should we wait it out or consult a behavior therapist?

like

What is the average salary for a management supervisor at bbdo San Francisco?

Trying to get a handle on retention bonuses. What are you seeing at your organizations? Across the board retention bonuses? Amounts based on level? Or reactionary when folks resign in an attempt to keep them?

like

Additional Posts in Excel Genius

Is there a way to consolidate non numerical data from multiple excel sheets into one master sheet?

like

Does anyone know how I can conditionally format a table with a sliding scale? I have a value that is supposed to fit in a range. I need the first possible range the number can fit in to be formatted. Thanks

likehelpful

I have a field in excel that represents the GSID, that I want to concatenate to create a link. Example field GSID is 123-west and the link needs to be www.website/123_west.html for some reason using concatenate with left & right formulas to switch - to _ results in the field throwing an error. Anyone knows of a way to make this work?

like

Best pdf to excel converter?

like

Just created my first macro! Super proud of myself! #humblebrag

likeuplifting

Can I write a macro that will fill out a table of information based on cells that have comments on them? For example, if G12 has a comment, I want to pull text from that comment + data in A12.

like

I’m trying to go back to the basics. How do you guys structure thoughts around identifying the correlation between 2 variables? A basic example: the number of steps I take in a day and the hours of sleep I got that night.

like

Please help. I was recently hired by a Venture Capital firm that uses MacBooks instead of PCs. I’m extremely efficient with Excel on PCs and am struggling with the transition to Mac. Do any Mac users have a list of keyboard shortcuts that they use frequently, or any other tips/tricks?

likefunny

How do I stop excel from changing the cell reference to a different column each time I refresh the data pull?
The spreadsheet is connected to an API data pull. Each time I refresh the data it changes the cell reference (SCD_0010) to a different cell e.g. (SCD_0127). How can I stop this?

Post Photo
like

Why when I refresh a query, the table where the query is loaded to just gets duplicate rows? Same with the pivot table that feeds off of that query?

like

Is there any easy way to handle FedEx/ups tracking numbers? I’d like to have some formula that would just show the status in another cell.

like

Hey, tips for where to look for good excel cheat sheets online? :)

I want to create a heat map of when people are unavailable with day of week as the columns and hour of day as the rows. I have start times and end times, how can I use this data to create the map?

like

What are the most annoying things for you in old-school excel users?
I’ll start:
1) vlookup 2) concatenate 3) inability to work with file versions, therefor constantly locking file from changes

like

Any recommend courses (coursera, edx, etc.) people would recommend for user with intermediate experience with excel?

like

A lot of the work at my new employer is quite redundant and can be automated via macros. What are some of the best resources to learn vba?

like

Google Sheets vs Excel - pros? cons? differences?

likefunnysmarthelpful

I have three lists. Each is a list of processes. Each list represents one hierarchy level. Is there an easy way to get from three separate lists to one list ordered like shown on the right?
Thanks for your help fish!

Post Photo
like

What is y'all's preferred clipping tool? One of my former companies had a great simple program that was only like $50 and worth every penny.
Snipping Tool just ain't cuttin' it (lol pun intended).

like

Hi! I have knowledge of pivot tables, vlook ups. But I'm rusty on if statements and would like more knowledge on excel formulas. I'm looking for excel classes to take online. Any suggestions?

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
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

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Send download link to your phone

OR

Scan your QR code to download
Fishbowl app on your mobile

By continuing you agree to Terms of Use and Privacy Policy.

Messaging rates may apply

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal