INDEX with Approximate MATCH

By Jeff Robson

Technical Difficulty: Advanced

Most people think of using INDEX with only an exact MATCH (i.e. a match type of zero).

Here’s an example of where you need something more flexible.

Pricing Bands Mixed with Flat Rates

Our client had received freight prices from their supplier to take freight items from their office to various other locations in Australia.

The prices for most depended on the quantity of items being shipped, but some states had a flat rate per shipment.

The client wanted a way to use this pricing table to automatically calculate their freight cost so they could check the prices their supplier charged were correct.

Solution

Firstly, we added a code that made each line unique by concatenating some of the fields together and turning the Quantity From into a two digit number (assuming they would never ship more than 99 items).

=[@Type]&”-“&[@[Start State]]&”-“&[@[End State]]&”-“&TEXT([@[Qty From]],”00”)

This formula was applied even to those destinations that had flat rates per shipment.

This allowed us to then use an INDEX with an approximate MATCH (match type of 1) on the Code column to determine freight costs for any quantity from 1-99 going to any state in the list … even though the prices were only shown either in bands or as flat rates.

=INDEX(Table1[Rate],MATCH(K6,Table1[Code],1))

(where K6 contains the concatenated code you’re looking for)

It’s almost like combining an exact MATCH with an approximate MATCH.