-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Approximate Lookup in Power Query
- Thread starter earthworm
- Start date
GraH
Well-known Member
- Joined
- Mar 22, 2020
- Messages
- 1,567
- Office Version
- 365
- Platform
- Windows
The easiest way I know that often works is.... Appending data.
Say you want to look a tax rate based on income and in your lookup table you'd have gross income as a reference threshold.
It's a matter of naming both columns alike.
Once appended, you can sort the table on the "lookup column", fill down the "looked up values" and filter out null values on one of the columns that's not in your lookup table.
One scenario where things might brake, is on text values with accented letters, as the sorting will not group them together. For example, if é,è or e all need to be treated as "e", then there is some clean-up required too.
Say you want to look a tax rate based on income and in your lookup table you'd have gross income as a reference threshold.
It's a matter of naming both columns alike.
Once appended, you can sort the table on the "lookup column", fill down the "looked up values" and filter out null values on one of the columns that's not in your lookup table.
One scenario where things might brake, is on text values with accented letters, as the sorting will not group them together. For example, if é,è or e all need to be treated as "e", then there is some clean-up required too.
Upvote
0
