Note: The other languages of the website are Google-translated. Back to English
English English

INDEX and MATCH with multiple arrays

Let’s say you have several tables with same captions as shown below, to lookup values that match the give criteria from these tables may be a hard job for you. In this tutorial, we will talk about how to lookup a value across multiple arrays, ranges or groups by matching specific criteria with the INDEX, MATCH and CHOOSE functions.

index match multiple arrays 1

How to lookup a value across multiple arrays?

To know the leaders of different groups that belong to different departments, you can first use the CHOOSE function to target the table to return the leader’s name from. MATCH function will then find out the position of the leader in the table where he/ she belongs to. Finally, the INDEX function will retrieve the leader based on the position information plus the specific column where the leaders’ names are listed.

Generic syntax

=INDEX(CHOOSE(array_num,array1,array2,),MATCH(lookup_value,lookup_array,0),column_num)

  • array_num: The number CHOOSE used to indicate an array from the list array1,array2,… to return the result from.
  • array1,array2,…: The arrays to return the result from. Here refers to the three tables.
  • lookup_value: The value the combination formula used to find the position of its corresponding leader. Here refers to the given group.
  • lookup_array: The range of cells where the lookup_value is listed. Here refers to the group range. Note: You can use the group range from any department since they are all the same and we just need to get the position number.
  • column_num: The column you indicate from which you want to retrieve data.

To know the leader of Group D that belongs to the Department A, please copy or enter the formula below in the cell G5, and press Enter to get the result:

=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)

√ Note: The dollar signs ($) above indicate absolute references, which means the name and class ranges in the formula won't change when you move or copy the formula to other cells. After you enter the formula, drag the fill handle down to apply the formula to the below cells, and then change the array_num accordingly.

index match multiple arrays 2

Explanation of the formula

=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)

  • CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): The CHOOSE function returns the 1st array from the three arrays listed in the formula. So it will return $B$5:$C$8, i.e, the data range of Department A.
  • MATCH(F5,$B$5:$B$8,0): The match_type 0 forces the MATCH function to return the position of the first match of Group D, the value in the cell F5, in the array $B$5:$B$8, which is 4.
  • INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2) = INDEX($B$5:$C$8,4,2): The INDEX function retrieves the value at the intersection of the 4th row and 2nd column of the range $B$5:$C$8, which is Emily.

To avoid changing array_num in the formula every time you copy it, you can use the helper column, the column D. The formula would be like this:

=INDEX(CHOOSE(D5,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)

√ Note: The numbers 1, 2, 3 in the helper column indicate the array1, array2, array3 inside the CHOOSE function.


Related functions

Excel INDEX function

The Excel INDEX function returns the displayed value based on a given position from a range or an array.

Excel MATCH function

The Excel MATCH function searches for a specific value in a range of cells, and returns the relative position of the value.

Excel CHOOSE function

The CHOOSE function returns a value from the list of value argument based on the given index number. For example, CHOOSE(3,”Apple”,”Peach”,”Orange”) returns Orange, the index number is 3, and Orange is the third value after index number in the function.


Related Formulas

Lookup values from another worksheet or workbook

If you know how to use VLOOKUP function to search for values in a worksheet, vlookup values from another worksheet or workbook won’t be a problem for you.

Vlookup with dymanic sheet name

In many cases, you may need to collect data across multiple worksheets for summary. With the combination of the VLOOKUP function and the INDIRECT function, you can create a formula to lookup specific values across worksheets with dynamic sheet name.

Multiple-criteria lookup with INDEX and MATCH

When dealing with a big database in an Excel spreadsheet with several columns and row captions, it’s always tricky to find something that meets multiple criteria. In this case, you can use an array formula with the INDEX and MATCH functions.


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
In sheet 1, I have a list of products about fifty different items and each one with a unique ID. On the next 12 columns is the price list for each month (Jan, Feb, Mar, Apr, May ... until Dec). Each month, the prices are slightly different. These products are to be distributed among 10 different persons with a unique ID (ex: P001) on sheet 2, I would like to have the data of the distributed items for P001 let's say for the month of Jan. how to get the price list referring to the column of Jan price list in sheet 1, Then next month, on sheet 2, if I type Feb, hot to get only the price list of Feb on sheet 1 and the same process for each month of the year.
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations