Multiple Column Lookup Approximate

[matt-gilbert]

Hi, I have 2 values (date and number) that I need to do an approximate (less than) lookup on 2 columns. I have attached relevant file with my current VLOOKUP formulas highlighted which only have 1 lookup value (number). I'm trying to add the date to this formula so that the lookup range will change depending on the date. I have tried INDEX MATCH MATCH and XLOOKUP but they only appear to work for exact matches.
Any help would be appreciated. Thanks in advance.
Matt

 

[p45cal]

What version of Excel are you using?

 

[matt-gilbert]

Microsoft 365 Version 2207 Build 16.0.15427.20182 64-bit

 

[p45cal]

In the attached is a formula in cell J10:
=WeeklyTax($F10,G10,$A$2:$D$19)
As you edit or type in this formula you get tool-tip clues as to what references need to go where:

Tell me if it works for you.
Change the date in F10 to observe the change in results.
I haven't included any rounding yet because I don't understand (read: can't reverse engineer) yours. Tell me in words the intention.

Even if it doesn't work for you, I've included some formulae in cells N10:W10 which are steps I used to put together a single formula. You should be able to use some of these, especially cells N10 and O10.

The formula in cell X10 uses LET to negate having to put the same reference into the formula multiple times and also uses 'variable' names suggesting their meaning: Gwage=Gross Wage, tbl=the lookup table, subtbl=sub table (that part of the bigger table used to look up rates etc.). This may help when trying to understand the formula.
Finally the formula in cell Y10 is a lambda formula for testing prior to making it a named lambda function (which I've used in cell J10).

If you want to transfer the lambda function to your workbook and you're not confident how to do it, just copy any single sheet (a blank one is simplest) from the attached workbook to your workook by right-clicking on its tab and choosing Move or Copy… and choose your workook from the dropdown in the top field To book:, and click OK. Then in your workbook straightaway delete the sheet you've just copied over, the lambda function will be in your workbook ready to use.

Your formulae in cells K4 and L5 are more incomprehensible, so again, tell me in words how they're meant to be calculated.

 

[matt-gilbert]

Hi p45cal,
Wow! Thanks you for your comprehensive response. Just when I thought I knew a bit about Excel...
I was hoping to copy formula from J10 and repeat down page for a larger data set (I'm going back over the years to calculate how much tax I should have paid for various different pay periods ie weekly, fortnightly and monthly). My dataset starts at 2007 through to 2022 and has a new tax table each year (only showed 2 years for this example sheet). I'll have to try and get my head around your lambda formula. I don't suppose there's a simpler formula (for an amateur like me!) which is "self contained" and doesn't need "helper" formulas? btw the original formulas in cells J3, K4 and L5 were courtesy of the Australian Tax Office...

 

[p45cal]

I don't suppose there's a simpler formula (for an amateur like me!) which is "self contained" and doesn't need "helper" formulas?

The function WeeklyTax is a standalone function which needs no helper cells.

 

[matt-gilbert]

Hi p45cal, sorry maybe helper wasn't the correct term. I have updated my spreadsheet to show a larger dataset and the formulas that are currently working that I'd like to replace. I have tried modifying your formulas (cells P1 & P2) however when I paste them into the cells below they return the correct result for the first four cells but not the last? Am I on the right track?

 

[p45cal]

Note that at the bottom of your lookup table, the bottom dates are 13th Oct 2020, the date in cell F9 is 10th July 2020 which is before those bottom dates.

I wouldn't have a GWage2 because the onl;y difference between it and GWage is the +.99 and that's only ever used once. This will save you having to enter a reference to the wage cell twice. (Note also that TRUNC(345.44,0) is the same as TRUNC(345.44), there is no need for the zero.)

Try deriving your lambda from the likes of:

=LET(myDate,$F5,GWage,TRUNC($G5),tbl,$A$4:$D$45,tblC1,INDEX(tbl,,1),subtbl,FILTER(tbl,tblC1=LOOKUP(myDate,tblC1)),
ROUND(XLOOKUP(GWage,INDEX(subtbl,,2),INDEX(subtbl,,3),,-1)*(GWage+0.99)-XLOOKUP(GWage,INDEX(subtbl,,2),INDEX(subtbl,,4),,-1),0))

[I don't understand the whys and wherefores of this truncating and adding .99. I'd like to understand.]

 

[matt-gilbert]

Hi p45cal,
I've finally got it all working (see attached).
Just wanted to let you see the outcome to close it out with you and help out others who might need this for calculating tax rates.
Thank you very much for your help with this.
Matt

PS - I have no idea why the ATO truncate and add .99. Refer ATO link on spreadsheet for further info

 

[p45cal]

Very good!