Forum Discussion
Can someone explain in laymans terms how this formula works?
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.
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.
- Thnk you Hans