Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Learn How to Lookup Items in Excel Using INDEX and MATCH

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

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc