Related Posts
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.
Guys .. has salary credited for you?
More Posts
Merry Christmas! 🎄
Additional Posts in Excel Genius
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?
Best pdf to excel converter?
Google Sheets vs Excel - pros? cons? differences?
New to Fishbowl?
unlock all discussions on Fishbowl.
Subject Expert
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)))
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.
Could that be because the data does not have and rows which cover those dates? Like, there is nothing to return?
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
=sumifs would probably work
If you can add a column to your data set, maybe this can help.
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
Subject Expert
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
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