Approximate Lookup with INDEX & MATCH Functions

If you wanted to do a lookup that wasn't an exact match, you're probably looking to do an approximate match lookup. There are different functions that can perform a closest match, partial match or even a fuzzy match. One of the function you can use is the INDEX & MATCH combination to find the closest value to a looked up value. This video will cover a simple example of looking up a salesperson sales quantity to figure out what kind of commision to be given.

Use the Excel Geography Data Type & Filled Map Feature

Learn how to use the Office 365 Geography Data Type feature along with the Filled Map charts.

If you've to the Office 365 subscription, you have access to a linked data type that goes online to pull back lots of interesting background information based on a geography value. For example if you want to know info about the population of India, just type in the country name, turn it into a geographic data type and Excel will go online and pull info like population or area size auto-magically. And you can do this for a list of countries if you're trying to do some analysis.

To add more to this Excel goodness, you can chart this data on a map with the feature called Filled Maps. It basically is a choropleth map that uses color or shade to help visualize the geographic data. Filled Maps is also an Office 365 enhancement, but it is looking to be available in Excel 2019.

Find Records that Don't Have a Match in Both Tables

This video covers the ANTI JOIN. This occurs in SQL when two tables returns rows from the first table where no matches are found in the second table. It's consider the opposite of a semi-join when in two tables the result returns rows from the first table where one or more matches are found in the second table. In this example an anti-join is finding records where there's no match between both tables. However in Excel Power Query, there is not anti join option when using the merge feature. However the workaround in to use the left and right anti join features and then append the results. Confused? Well check out the video to see how it could be done.