Two-Way Lookups in Excel
If you are a Excel user then invariably you must've have used the lookup functions of Excel, namely HLookUp() and VLookup(). For those who haven't: A lookup function is used to return a value from a given table by looking up another value in the same table. A simple example could be a discount table consisting of two columns, Purchase Amount & Discount. You can write a formula that usesVLookUp() to determine the discount rate for a given purchase amount.
Unfortunately the lookup functions in Excel are only appropriate for one-way lookups. There is no inbuilt worksheet function for performing a two-way lookup. Let me share my 2-way lookup formula.
Consider the table (refer to the figure) which displays the rather dismal monthly sales figures of various computer peripherals.
To arrive at the Sales figure for Modems sold in Feb
We shall make use of two functions Match() and Index() to create our two way lookup. Let me discuss briefly how these functions are used. For more detailed info look up the Excel help file.
My 2-Way lookup formula:
=INDEX(DataRange, MATCH(TheProduct,Products,0), MATCH(TheMonth,Months,0))
This is the formula in Cell I5.
MATCH(TheProduct,Products,0) will look up the value of the range TheProduct in the array Products and will return the position of the first occurrence of the value. If TheProduct is Feb then the above call will return 2 as Feb occurs at the second position in our array (Jan, Feb, Mar, Apr)
Similarly MATCH(TheMonth,Months,0) will look up the value of the range TheMonth in the array Months and will return the position of the first occurrence of the value. If TheMonth is Modems then the above call will return 2 as Modems occurs at the second position in our array (Printer, Modems, Scanners, Plotters)
Hence the above formula reduces to: =INDEX(DataRange, 2, 2) which returns the value of cell in the Range DataRange which occurs at the intersection of the 2nd Row and 2nd Column i.e 45. Yahoooooo!!!
For Advanced users:
Now the formula will return an error in case the Product or Month does not exist in the array. So let us use IsError() and If() to arrive at a more refined formula which will return 0 if either Product or Month does not exist in the Array.
=IF(ISERROR(INDEX(DataRange, MATCH(TheProduct,Products,0), MATCH(TheMonth,Months,0))),0,INDEX(DataRange, MATCH(TheProduct,Products,0), MATCH(TheMonth,Months,0)))
Copyright 1999-2011 (c) Shyam Pillai. All rights reserved.