# Google sheet: how to use multiple parameters based on a certain value?

1. ## Google sheet: how to use multiple parameters based on a certain value?

I'm working on a tax sheet for personal use in Google Sheets. The hard thing is that the tax rate has been different for the last three years, making it hard for me to make the correct formula.

I have two tabs in the sheet
- Main sheet (showing taxes and tax rate
- Tax calculation sheet

The tax sheet has a calculation now based on a fixed percentage for taxes, but I want it to calculate based on the correct tax rate for the correct year, based on the date for the row.

On the main sheet I have listed:
A5 = Year 2017
A6 = Year 2018
A7 = Year 2019
B5 = 2017 tax calculations
B6 = 2018 tax calculations
B7 = 2019 tax calculations
C5 = 2017 Tax % rate
C6 = 2018 Tax % rate
C7 = 2019 Tax % rate

In the Tax sheet I add one row for each trade which includes the profit made - shown in the V column, then profits multiplied with the tax rate which is taking place in the W column. The code used today is =\$V9*24%. The date for the trade is taking place in the H column. What I want to do is something like this:

If the date is >=1/1/2017",\$H\$2:\$H\$991,"<1/1/2018, then multiply the profits in V column with C5.
If the date is >=1/1/2018",\$H\$2:\$H\$991,"<1/1/2019, then multiply the profits in V column with C6.
If the date is >=1/1/2019",\$H\$2:\$H\$991,"<1/1/2020, then multiply the profits in V column with C7.

All of these conditions need to be set in a single formula to be used in the W column.  Register To Reply

2. ## Re: Google sheet: how to use multiple parameters based on a certain value?

Hi,

Formula:  `Please Login or Register  to view this content.`

It not working share sample file (visual are more interactive than theory)

*If solved click * and mark as SOLVED  Register To Reply

3. ## Re: Google sheet: how to use multiple parameters based on a certain value?

It didn't work with your formula, but I have attached a sample where you can see how the spreadsheet looks like.  Register To Reply

4. ## Re: Google sheet: how to use multiple parameters based on a certain value?

Hi cryptomike,

Check this out, actually the tax value was not in V, But have shared the updated file.  Register To Reply

5. ## Re: Google sheet: how to use multiple parameters based on a certain value?

Hi,

It calculated the tax amount based on the V column, but maybe your solution works too. What did you change? Because the problem was not on the "Main" sheet, but the "Tax" sheet.  Register To Reply

6. ## Re: Google sheet: how to use multiple parameters based on a certain value? Originally Posted by cryptomike . . .

On the main sheet I have listed:
...
C5 = 2017 Tax % rate
C6 = 2018 Tax % rate
C7 = 2019 Tax % rate

In the Tax sheet I add one row for each trade which includes the profit made - shown in the V column, then profits multiplied with the tax rate which is taking place in the W column. . . .

If the date is >=1/1/2017",\$H\$2:\$H\$991,"<1/1/2018, then multiply the profits in V column with C5.
If the date is >=1/1/2018",\$H\$2:\$H\$991,"<1/1/2019, then multiply the profits in V column with C6.
If the date is >=1/1/2019",\$H\$2:\$H\$991,"<1/1/2020, then multiply the profits in V column with C7.

All of these conditions need to be set in a single formula to be used in the W column.
Maybe

W2: =V2*lookup(year(H2),{2017;2018;2019},main!\$C\$5:\$C\$7)  Register To Reply

7. ## Re: Google sheet: how to use multiple parameters based on a certain value? Originally Posted by hrlngrv Maybe

W2: =V2*lookup(year(H2),{2017;2018;2019},main!\$C\$5:\$C\$7)
This worked like a charm. Thank you!  Register To Reply

8. ## Re: Google sheet: how to use multiple parameters based on a certain value?

Cross-posted here  Register To Reply

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Tags for this Thread #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1