Apr 25 2021 08:05 AM
Back in June of 2020 I ask the question that can be seen on this thread..
and @Sergei Baklan kindly provided the answer below that worked perfectly.
=INDEX(Sheet1!A:A,MATCH(Sheet2!$B2,Sheet1!B:B,0))
Can someone kindly break it down and explain in laymans terms exactly what it is doing as I need to reuse in on a different worksheet and I am having so much trouble working out what references I need to change and so on.
Rather than just asking for help with the new workbook and as this is such a neat formula, I really want to learn what it is doing / how it works and expnd my knowledge?
Apr 25 2021 08:18 AM
Apr 25 2021 08:27 AM
Apr 25 2021 08:48 AM
SolutionMATCH(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.
Apr 25 2021 10:35 AM
Apr 25 2021 11:23 AM
Apr 25 2021 11:25 AM
Apr 25 2021 08:48 AM
SolutionMATCH(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.