Index/Match formula

[jsp33]

I am trying to pull data from one worksheet into another worksheet. I think using Index/Match is the way to go, but I'm not sure how (I tried Vlookup and don't think that's the right formula).

Worksheet one has numbers in column A (say 1 to 10), and in Column C has the data (Town visited) I want to pull in to worksheet two. Worksheet 2 has the SAME numbers, with names associated with the numbers, and so on occasion there may be 2 or 4 number 4's with different names. I want to use the numbers in column A of both worksheets to grab the Town and match it up with the name. So in the second worksheet, I want everything that has a 1 to have 'Chicago' next to it, and everything that has a 2 to have 'New York', etc. See below for an example
[TABLE="width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD]Tuesday
[/TD]
[TD]Chicago
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Wednesday
[/TD]
[TD]New York
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Tuesday
[/TD]
[TD]Atlanta
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Friday
[/TD]
[TD]San Francisco
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Saturday
[/TD]
[TD]Austin
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]WORKSHEET 1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]WORKSHEET 2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Jane Smith
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]John Smith
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Sam Smith
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Steve Jones
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Allen Stevens
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]ANSWER I'M LOOKING FOR
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Jane Smith
[/TD]
[TD]Chicago
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]John Smith
[/TD]
[TD]Chicago
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Sam Smith
[/TD]
[TD]Chicago
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Steve Jones
[/TD]
[TD]New York
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Try, assuming both your sheets start in row 1:

=VLOOKUP(A1,Sheet1!$A$1:$C$100,3,0)

If you have headers, change the 1s in the formula accordingly.

 

[kweaver]

Try this:

Excel 2010
	A	B	C
1	1	Jane Smith	Chicago
2	1	John Smith	Chicago
3	1	Sam Smith	Chicago
4	2	Steve Jones	New York
5	3	Allen Stevens	Atlanta
PeopleCities

Cell Formulas
Range	Formula
C1	=VLOOKUP(A1,Cities!$A$1:$C$5,3,FALSE)

 

[jsp33]

Try, assuming both your sheets start in row 1:

=VLOOKUP(A1,Sheet1!$A$1:$C$100,3,0)

If you have headers, change the 1s in the formula accordingly.

Thank you, that worked. I tried a Vlookup and it did not work, not sure what I did wrong!

 

[Eric W]

The functions can be finicky, if you put a comma or a zero in the wrong place, it won't work.

Glad we could help!