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.
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.
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
The Excel INDEX function returns the displayed value based on a given position from a range or an array.
The Excel MATCH function searches for a specific value in a range of cells, and returns the relative position of the value.
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
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
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, Edge and Firefox.
