Skip to content

Email Newsletter Challenge 15

This week’s challenge will test your ability to perform an approximate match lookup. Think you’re up to the challenge?

Download the spreadsheet challenge using the links below.

The Challenge

This time, your task is to write a formula to assign the appropriate tax rate to each person based on their income.

The Solution

The solution is the same for both Excel and Google Sheets.

Start by selecting cell D4, and enter the following formula.

=LOOKUP(C4, G4:G10, F4:F10)

The LOOKUP function searches for the Income number (C4) within the Minimum Income amounts (G4:G10). The LOOKUP function will search for the largest number in G4:G10 that is less than or matches the lookup value (C4).

Finally, the LOOKUP function will return the corresponding tax rate from F4:F10.

Your next step is to make the 2nd and 3rd argument absolute references.

=LOOKUP(C4, $G$4:$G$10, $F$4:$F$10)

This enables you to copy the formula down with the fill handle to assign tax rates to every person in the list.

Thanks for challenging yourself this week, and I hope you had fun and learned something new.

And if you’re not signed up for my weekly email newsletter, you can sign up right here!

This Post Has 3 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top