Concatenating datas from different sheets

Question

I have an Excel workbook with 2 sheets. Sheet #1 contains 2 columns, names and points.

| A         | B  |
|-----------|----|
| John Doe  | 65 |
| Jolly Doe | 45 |
| Hello Doe | 50 |

Sheet #2 contains just one column, names.

| A         |
|-----------|
| John Doe  |
| Hello Doe |

I want to get points of every name which in sheet #2. So result should be like this:

| A         | B  |
|-----------|----|
| John Doe  | 65 |
| Hello Doe | 50 |

Using this formula on Sheet 2 to get points but getting John Doe now point of John's

=VLOOKUP(A1;Sheet1!A1:A10;1)

Can you show me a way to achieve this?

Answer 1

You are very close with your formula.

Use:

=VLOOKUP(A2,Sheet1!$A$2:$B$99999,2,FALSE)

Your reference to Sheet2!B1:B999 should be Sheet1!$A$2:$B$99999. Your original is actually pointing to the place where you want to put the results, not the source of the numbers.

The Vlookup() function needs:
What you are looking for - A2
Where you want to look - Sheet1!$A2:$B99999 (your look up table)
What you want to return - 2 (the second column of your table)
If you want a close but not exact match - False

By putting the $ in the range $A$2:$B$99999, the reference to the table will be fixed. So when you copy the formula down through the rest of the cells, the look up table range will not change.