Microsoft Excel Tutorial: Matching data between two Excel worksheets where you need to match both the city and the tax rate in order to find the match. This video shows three different methods.
Welcome to another episode of the MrExcel Podcast, where we dive into all things Excel. In today's episode, we will be discussing a two-way lookup, but with a twist - we have three different ways to solve it. This question was sent in from a viewer in Minnesota who has two worksheets and needs to do a VLOOKUP, but with the added complication of having two tax rates for the same city.
The first and easiest way to solve this is by using XLOOKUP, but not everyone has access to this function. So, we will also explore two other methods using VLOOKUP and Power Query. With XLOOKUP, we can use the CONCATENATE function to combine the city and rate columns and then use that as our lookup value. This allows us to easily find the correct tax code for the given city and rate.
If you don't have XLOOKUP, don't worry - we have two other methods for you. The second method involves creating a concatenated key column in our VLOOKUP table and using that as our lookup value. This simplifies the VLOOKUP formula and makes it easier to find the correct tax code. Lastly, we explore using Power Query to merge two separate tables and create a connection between them. This allows us to easily refresh the data and find the correct tax code.
In this episode, we also discuss the importance of adding an index column when using Power Query and how it can affect the order of our data. We also touch on the different methods for creating an index column and how to ensure that our data stays in the correct order. If you want to learn more about these methods and other Excel tips and tricks, be sure to check out my book, "MrExcel 2021 Unmasking Excel".
If you found this episode helpful, please don't forget to like, subscribe, and ring the bell to be notified of future episodes. And as always, feel free to leave any questions or comments down below. Thank you for tuning in and we'll see you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: [ Ссылка ]
You can help my channel by clicking Like or commenting below: [ Ссылка ]
Table of Contents
(0:00) Welcome
(1:01) Using XLOOKUP for two-way match
(2:15) Using VLOOKUP with concatenated key
(3:18) Using three queries in Power Query for two-way lookup
(07:28) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
Concatenation in Excel
Data merging in Excel
Exact match in VLOOKUP
Learn Excel from MrExcel Podcast episode 2443
Lookup table
MrExcel 2021 Unmasking Excel book
Power Query
Results array
Sales tax file
Two-way lookup
VLOOKUP
XLOOKUP
Join the MrExcel Message Board discussion about this video at [ Ссылка ]
![](https://s2.save4k.ru/pic/njucFfrYna8/maxresdefault.jpg)