Cross Sheet Formula for looking up a value when several columns meet a criteria. INDEX, MATCH, AND?

Marita L

Help needed! 🙏

I have tried many variations of IF(AND...INDEX & MATCH Statements and I am so lost with what I should be using in this situation. 😓

I am working on a payroll calculator. I need to return a value for the Tentative Amount To Withhold based on Filing Status and the Adjusted Annual Wage.

I think my formula needs a combination of INDEX, MATCH, IF & AND but I am not sure which one I should start with.

First it needs to consider the Filing Status with the Adjusted Annual Wage on Payroll Calculator Sheet, and return the Tentative Amount To Withhold from my Tax Rate Table based on those two criterion.

I know I will need to incorporate greater or equal to and less than or equal to to match the Adjusted Annual Wage with the Tentative Amount To Withhold but not sure where to start.

Guidance appreciated!

FormulaScreenshot.jpg

FormulaScreenshot02.jpg

Kelly Moore

Hi Marita

Does this get you what you need? Remember when you using cross sheet references you'll need to create these terms by clicking the blue Reference Another Sheet Link. You can't just copy paste this in.

=INDEX(COLLECT({Test Sheet Expected Withholding}, {Test Sheet At Least}, [Adjusted Annual Wage]@row >= @cell, {Test Sheet But Less Than}, @cell >= [Adjusted Annual Wage]@row, {Test Sheet Tax Status}, @cell = [Tax Status]@row), COUNTIFS({Test Sheet At Least}, [Adjusted Annual Wage]@row >= @cell, {Test Sheet But Less Than}, @cell >= [Adjusted Annual Wage]@row, {Test Sheet Tax Status}, @cell = [Tax Status]@row))

The INDEX function expects two bits of data - the range which our COLLECT function gathers based on criteria. It also needs a row index. Since I don't know that this is always a '1', I get a value using a COUNTIFs, using the same criteria that was in the COLLECT function.

In your screen shot I didn't know how the % was used in your formula. Let me know if/how these are used and we can tweak this formula - if this formula is close to what you need.

cheers

Kelly Moore

Marita

If it were me, I would probably do what you're describing with the separate columns - not only so I could make sure the correct value was being pulled, but also, if there is a error I could see which component had the error. If rolled into one cell then the cell would show ERROR but be unable to point you to the culprit.

That being said, if you wanted it as a single calculation we would drop in the formulas just as you have written and combine them with mine. To do this we wouldn't open the closing parenthesis of my formula and add on there- we keep my entire formula add a plus sign (or whatever algebraic sign it is) AFTER my closing parenthesis, drop your entire first formula and so on. Picture each entire formula as a single number and build your overall formula that way. Hopefully what I just said made sense rather than be confusing.

Let me know if I can help

Kelly

Kelly Moore

Hi Marita

Does this get you what you need? Remember when you using cross sheet references you'll need to create these terms by clicking the blue Reference Another Sheet Link. You can't just copy paste this in.

=INDEX(COLLECT({Test Sheet Expected Withholding}, {Test Sheet At Least}, [Adjusted Annual Wage]@row >= @cell, {Test Sheet But Less Than}, @cell >= [Adjusted Annual Wage]@row, {Test Sheet Tax Status}, @cell = [Tax Status]@row), COUNTIFS({Test Sheet At Least}, [Adjusted Annual Wage]@row >= @cell, {Test Sheet But Less Than}, @cell >= [Adjusted Annual Wage]@row, {Test Sheet Tax Status}, @cell = [Tax Status]@row))

The INDEX function expects two bits of data - the range which our COLLECT function gathers based on criteria. It also needs a row index. Since I don't know that this is always a '1', I get a value using a COUNTIFs, using the same criteria that was in the COLLECT function.

In your screen shot I didn't know how the % was used in your formula. Let me know if/how these are used and we can tweak this formula - if this formula is close to what you need.

cheers

Marita L

That worked perfectly @KDM!!! This formula is greatly appreciated. I was ready to do helper columns and create a much more complex tax table for my source sheet, now I can just update my source table annually! 👏💪

To complete my withholding calculations I need to take the value from the Annual Adjusted Wage column, calculate how much it exceeds the At Least value, multiply that by the % indicated in the Tax Table and add that amount to the Tentative Withholding amount that your formula returned for me.

I know how to INDEX and MATCH to return the correct At Least value and Percent value to add those to my payroll calculator to complete the calculations, but I would not know how to add that to your formula to do all the calculations within one column by referencing my 2021 Tax Table source sheet.

Kelly Moore

Hey Marita

You mention that you know how to do the calculations you still need to add. Are those values already in your target sheet? The sheet where my formula is located? If yes, we should be able to just add it to mine. =my formula + (your Matched At Least Value@cell *your Matched percentage@cell)

The column names would need to be changed to match your actual columns

Did that work? 🤞 Let me know if we need to tweak more

Kelly

Marita L

Hey Kelly,

I think that would work, it would eliminate one column at least.

Currently I have your formula which returns the "Tentative Amount To Withhold". In the next column over I am using this formula to return the percent value from my 2021 Tax Table source sheet:

=INDEX({2021 Tax Table Percent}, MATCH([Tentative Amount To Withhold]@row, {Tentative Amount To Withhold}, 0))

The next column returns the At Least value from my source sheet.

=INDEX({2021 Tax Table At Least}, MATCH([Tentative Amount To Withhold]@row, {Tentative Amount To Withhold}, 0))

Then the following column calculates the difference between the Tentative Amount and the At Least Value, it multiplies that by the matched percent.

The next column adds that value to to the Tentative Amount To Withhold, which gives me the number I was looking for using my 2021 Tax Table source sheet. 👌

I'm thinking I might just leave those values in their separate columns rather than doing all of the calculations within one. Only for the reason that it is easier for me to see the calculations and values returned separately and I can manually check it to make sure I'm getting the correct result. At least until I gain more confidence in my ability to understand and utilize more complex smartsheet formulas and functions!

Appreciate the forums here, it has been VERY helpful!

Best,

Marita

Kelly Moore

Marita

If it were me, I would probably do what you're describing with the separate columns - not only so I could make sure the correct value was being pulled, but also, if there is a error I could see which component had the error. If rolled into one cell then the cell would show ERROR but be unable to point you to the culprit.

That being said, if you wanted it as a single calculation we would drop in the formulas just as you have written and combine them with mine. To do this we wouldn't open the closing parenthesis of my formula and add on there- we keep my entire formula add a plus sign (or whatever algebraic sign it is) AFTER my closing parenthesis, drop your entire first formula and so on. Picture each entire formula as a single number and build your overall formula that way. Hopefully what I just said made sense rather than be confusing.

Let me know if I can help

Kelly