Selected Answer

This formula should do the job.

`=INDEX(Sheet2!$B:$B,MATCH($M2,Sheet2!$A:$A,0))`

Just in case it doesn't, knowing how it works would enable you to tweak it. First the MATCH() function.

`=MATCH($M2,Sheet2!$A:$A,0)`

The instruction is to look for a[n exact] match of the value in M2 (of the sheet where the formula resides) in Sheet2's column A. Simple enough. You can actually put this in a cell in Sheet1 and it will return a number if a match is found.

The INDEX() function isn't any more difficult. It specifies a cell using row and column coordinates. For example, cell(15, 1) would be in the 15th row of the first column. All you need to know is where to start counting. Therefore the function provides that information first.

`=INDEX(Sheet2!$B:$B,15)`

You can put that in a worksheet cell, too (on Sheet1) and it will return the value of the 15th row of Sheet2!$B:$B. Note that the column number has been omitted. That is OK because the range $B:$B only has one column.

Now all you have to do is to replace the static 15 with the variable value provided by the MATCH() function and you arrive at the formula first above given.

If I understood your requirement correctly, the formula below should do the same job.

`=VLOOKUP($M2,'Sheet2'!$A:$B,2,FALSE)`