Forum Discussion
Solved
Can someone explain in laymans terms how this formula works?
Back in June of 2020 I ask the question that can be seen on this thread.. Previous Question and SergeiBaklan kindly provided the answer below that worked perfectly. =INDEX(Sheet1!A:A,M...
MATCH(Sheet2!$B2,Sheet1!B:B,0)Looks for the value of cell B2 on Sheet2 in the entire column B of Sheet1. The 0 at the end tells Excel to look for an exact match.
MATCH returns the index of the first cell where that value is found. For example, if the value is found in the 12th cell of column B, MATCH returns 12. If there is no match, it returns the error value #N/A (not available)
The INDEX function looks in column A on Sheet1, and returns the cell with the index returned by MATCH. So for example, if MATCH returned 12, the final formula will return the 12th cell of column A, that is A12.
Basically.
MATCH formula : returns the "serial number" of a searched item in a list of cells.
INDEX formula : returns the value of the "cell" at a particular "serial number" in a list of cells.
These are 2 different formulas which can be used in combination.
Sometimes you want that "find a particular value in list 1 and if it is found at serial number say 12, then as a second step, find the value at that serial number (12th) in another list"
So you see, the above requirement can be broken down in 2 steps, finding the "serial number" of interest in a list (=use of MATCH formula) and then in second step finding the "value at that serial number" in another list (=use of INDEX formula)
By using the MATCH formula inside of an INDEX formula you are combining the two steps to do the complete job.
Note that the technical term used for "serial number" in excel is "index".
The MATCH and INDEX formula have more details which you would need to study in Excel Help site, or some other such tutorial sites over internet or in other replies of this post.
Suggestion : First learn to use MATCH formula in isolation. Then learn to use INDEX formula in isolation. After you have understood them, then learn to use them in combination as done in your post example.
MATCH formula : returns the "serial number" of a searched item in a list of cells.
INDEX formula : returns the value of the "cell" at a particular "serial number" in a list of cells.
These are 2 different formulas which can be used in combination.
Sometimes you want that "find a particular value in list 1 and if it is found at serial number say 12, then as a second step, find the value at that serial number (12th) in another list"
So you see, the above requirement can be broken down in 2 steps, finding the "serial number" of interest in a list (=use of MATCH formula) and then in second step finding the "value at that serial number" in another list (=use of INDEX formula)
By using the MATCH formula inside of an INDEX formula you are combining the two steps to do the complete job.
Note that the technical term used for "serial number" in excel is "index".
The MATCH and INDEX formula have more details which you would need to study in Excel Help site, or some other such tutorial sites over internet or in other replies of this post.
Suggestion : First learn to use MATCH formula in isolation. Then learn to use INDEX formula in isolation. After you have understood them, then learn to use them in combination as done in your post example.
Thnk you amit