How do you get excel to quickly perform 160,000 lookups against 160,000 different unique records and get an exact match, for a total of up to 25,600,000,000 different operations?
Basically there is a fast way to do this that quickly produces results in a minute or so – and a slow way to do this that checks all combinations and could take hours.
-
- The fast way is to use IF formula to check if index match found an exact match on a sorted table, if it did not then don’t use the result, if it did then use the result.
- =IF(A3=INDEX(‘Rate Table’!$B$1:$B$145390,MATCH(O3,’Rate Table’!$B$1:$B$145390)),INDEX(‘Rate Table’!$A$1:$A$145390,MATCH(A3,’Rate Table’!$B$1:$B$145390)),”NONE”)
Why would you need to do this?
Maybe you have a huge list of names and you want to look up their phone number in a directory, or vice versa.
In our case we were analyzing the costs of 160,000 different phone calls against different rate plans. Each call could be billed at any of 160,000 different rate plans. (While consumer rates are simple – wholesale rates are quite complex – all numbers starting with the NPA NXX 212-555 may have a different rate from all numbers starting with 212-556.)
You want excel to look at the call and see where the call was made to and what the rate should be. For example a call is to the 212-555 NPA NXX. You therefore look up on the rate table what the rate is for calls to the 212-555 NPA NXX. You then have excel do this for every call you make.
Calls Table
A B
NPA+NXX Rate
1212555 ?
1212557 ?
1212555 ?
1212556 ?
Rate Table
A B
NPA+NXX Rate
1212555 .05
1212556 .06
1212557 .04
The ‘calls table’ needs a formula in column B that will return a rate.
An easy was to do this on a small scale is to use Index Match.
Let’s say for the call you want to look up you have the NPA NXX in cell A3, column A row 3.
This would look up the price of the call.
=Index({column with prices},Match(A3,{column with NPA NXXs},0)
Note: {column with prices} and {column with NPA NXXs} could be on another sheet and should have absolute values so you can copy and past your formula down the table of calls you want to look up.
{column with prices} = ‘Rate Table’!$B$1:$B$145390
{column with NPA NXXs} = ‘Rate Table’!$A$1:$A$145390
Note that 0 at the end of the formula =Index({column with prices},Match(A3,{column with NPA NXXs},0) ? It is important. It indicates an exact match, we don’t want excel to return a false positive. If it can’t find a match we don’t want want it return the rate for some other NPA NXX. If you don’t put in the 0 excel WILL return the rate for the closest NPA NXX.
The problem with an exact match is that excel will search the entire column for an exact match until it finds one. So with a rate table with 160,000 entries it may check as many as 160,000 cells each time you run the formula. If you check 160,000 calls, you then have up to 25 billion operations. This will a PC a long time.
So how do you get excel to quickly look up the rate and get an exact match?
Fortunately excel will quickly look up rates on a sorted table – but there is no way to get it only return an exact match, it will always return an inexact match. (Excel can quickly do a look up because it uses binary search – it looks up row 80,000 and if the result is above what it is looking it looks at row 40,000, and is the result at row 40,000 is below what it is look for then looks at row 60,000, and so forth and so on. http://en.wikipedia.org/wiki/Binary_search_algorithm)
There is a work around.
Basically you tell excel – lookup the NPA NXX, tell me what row it is on, and then tell me what NPA NXX is at that row.
The column on the rate table with NPA NXXs must be sorted.
=Index({column with NPA NXXs},Match({NPA NXX},{column with NPA NXXs})
So now you can see what NPA NXX excel found on the rate table and if it is different from the NPA NXX on the call table.
Excel has an IF formula – so you can tell excel to do a look up of the rate ONLY if the NPA NXX it finds is equal to the NPA NXX it is looking up.
=IF({NPA NXX}={NPA LOOKUP},{Rate LOOKUP},”NONE”)
=IF(A3=INDEX(‘Rate Table’!$B$1:$B$145390,MATCH(O3,’Rate Table’!$B$1:$B$145390)),INDEX(‘Rate Table’!$A$1:$A$145390,MATCH(A3,’Rate Table’!$B$1:$B$145390)),”NONE”)
The last trick to get this to work is to make sure that the first entry in your table sorted lowest to highest is lower than the lowest entry on your table of NPA NXXs. So we would put a 1, as all NPA NXXs are higher than one.
This means that we’ll always get either exact rate OR the word NONE in the cell. We won’t get an error or inexact match.
Postscript:
We post this because we had problem figuring out the fastest way to do this. We tried using countif to determine if our entry in the call table shows up in the rate table- but that is slow too. Since Index Match against a sorted list uses binary searches it is faster. We’d love to hear of a fast way to do this in excel – but this seem work just fine for comparing ten of billions of combinations – provided there is sorting.
You can leave a response, or trackback from your own site.