understanding index and match look up question

Patricia Timm
Patricia Timm used Ask the Experts™
on
Can you explain the following
=INDEX($M10:$CH10,1,MATCH($A$6,Function,0))  
I understand the range is M10 thru Ch10 , 1 is the 1st row in M10 thru Ch10? then I match on whatever is in A6 lets say Accounting not sure what function does and I think the 0 means that it has to be an exact match. I am trying to understand what this formula based on the components is doing. Is it populating the cell based on the formula in the cell? thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sam JacobsCitrix Technology Professional / Director of TechDev Services, IPM

Commented:
Function is a bit misleading … it's just a named cell range.
And yes, the 0 means it has to be an exact match.
MATCH will return the column number for the INDEX function.
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
In Excel, formulas are placed in cells, and their results populate those cells.

In the formula =INDEX($M10:$CH10,1,MATCH($A$6,Function,0)), you apparently have a named range called Function. MATCH is searching for the value in cell A6 within named range Function. The 0 means MATCH is looking for an exact match, and the data in Function isn't necessarily sorted.

MATCH returns an index number. If A6 matches the first cell in Function, then MATCH returns 1. If it matches the fourth cell in Function, then MATCH returns 4.

I speculate that named range Function is in columns M:CH in some row other than 10. So when you find the index number of the match for A6 in Function, you look for the corresponding value (same column) in row 10 for the value to return from INDEX.
Kesavan JeganarayananIT Consultant
Commented:
INDEX – get value at known position
MATCH – find position in a list
Here is the link for very basic understanding of Index and match functions in excel
https://exceljet.net/index-and-match

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial