Lookup on another sheet in Excel?

Question

I have a very simple Excel book that I am trying to spiffy up. Here's what it looks like:

Sheet 1:
ID    Name
1     Bob
2     Joe
3     Earl

Sheet 2:
Name  ID  Whatever

I would like when I type a name on sheet two for it to match it to a name in sheet 1 and fill in the id for that row in the field in sheet 2. So If I type "Joe" in sheet two it will fill in 2 in the id column.

My problem is slightly more complex than this, but I believe this is the crux of it for now.

Answer 1

You can use VLOOKUP to look up values that are to the right of the key. INDEX and MATCH can be used to look up values to the left of the key.

The formula in this case on Sheet2 would be

=INDEX(Sheet1!A:A,MATCH(A2,Sheet1!B:B,0))