Can someone explain in laymans terms how this formula works?

[Belinea2010]

Back in June of 2020 I ask the question that can be seen on this thread..

Previous Question 

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?

[#2]

This may help:
https://exceljet.net/index-and-match

[#3]

Hi yes I have been using that page to try and work thing out but its over my head which is what I asked for a laymans walkthrough.

[#4 · Accepted solution]

@Belinea2010 

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.

[#5]

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.

[#6]

Thnk you Hans

[#7]

Thnk you amit