Apart from VLOOKUP, we can lookup items in excel using a combined formula of INDEX and MATCH functions. This is more popular than VLOOKUP. MATCH FUNCTION returns the numeric position of an item in a list. INDEX FUNCTION returns the VALUE at a known position. The steps below will walk through the process.
Figure 1- Lookup with INDEX and MATCH functions
Formula
=INDEX(C4:E13,MATCH(H3,B4:B13,0),MATCH(H4,C3:E3,0))
Setting up the Data
We will use the INDEX and MATCH functions to get the February Sales for New York Branch of a Pharmacy Store with Branches across the US
- The Branches of the store will be entered into Column B
- Column C, D, and E will contain the Sales for 3 consecutive months
- The result will be returned in Cell H5
Figure 2 – Setting up the Data
Lookup New York February Sales
- We will click on Cell H5
- We will insert the formula below into Cell H5
=INDEX(C4:E13,MATCH(H3,B4:B13,0),MATCH(H4,C3:E3,0))
- We will press the enter key
Figure 3- New York February Sales with the INDEX and MATCH Functions
Explanation
=INDEX(C4:E13,MATCH(H3,B4:B13,0),MATCH(H4,C3:E3,0))
=INDEX(array,row_num,column_num)
- Row number:
Match(lookup_value,lookup_array,match_type)
- Column Number:
(Match(lookup_value,lookup_array,match_type))
In this formula, the INDEX FUNCTION is used to return the value within an array at the specified row number and column number by the MATCH FUNCTION. The returned row to INDEX is 4 and the column is 2.
=INDEX(C4:E13,4,2)
The INDEX function will check the range (C4:E13) and return the value in row 4 and column 2.
The first MATCH function gets the Row number of New York in the range (B4:B13) and returns as 4. The second MATCH function gets the Column number for the Month (February Sales) in the range (C3:E3) and returns as 2. This is then returned to INDEX which delivers the result.
0 in the formula means that we want an exact matching value.
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment