Excel Cross Sheet Lookup Formula

问题:

I am reaching my limits with Excel at the moment and could use a few pointers. I have a list of users and the teams they are in as well as an event number. I want to be able to cross reference which event number the teams are scheduled for and populate that entry into a separate field. Then breakdown how many users per office are at each event. I will give an example below as it's probably easier.

Sheet 1 - The numbers in columns 1,2 and 3 are purely the number from that team in that

<table><tbody><tr><th>Team Name</th><th>Number in Team</th><th>1</th><th>2</th><th>3</th></tr><tr><td>Team A</td><td>5</td><td> </td><td> </td><td>5</td></tr><tr><td>Team B</td><td>12</td><td>12</td><td> </td><td> </td></tr><tr><td>Team C</td><td>15</td><td> </td><td>5</td><td> </td></tr></tbody></table>​

Sheet 2

<table><tbody><tr><th>Team Name</th><th>User Name</th><th>Location</th><th>Event</th></tr><tr><td>Team A</td><td>Bob</td><td>London</td><td>3</td></tr><tr><td>Team A</td><td>Rick</td><td>Glasgow</td><td>3</td></tr><tr><td>Team A</td><td>Mary</td><td>Glasgow</td><td>3</td></tr><tr><td>Team B</td><td>Sue</td><td>London</td><td>1</td></tr><tr><td>Team B</td><td>Jill</td><td>Brighton</td><td>1</td></tr><tr><td>Team C</td><td>Sally</td><td>London</td><td>2</td></tr><tr><td>Team C</td><td>Mark</td><td>Brighton</td><td>2</td></tr><tr><td>Team C</td><td>Harry</td><td>London</td><td>2</td></tr></tbody></table>​

I am trying to write a formula that for looks in sheet 1 for a team and then finds which event(s) they are on and then populate that value in the events column of sheet 2. From there I can work out how many per office are being are at each event.

I hope I have explained it OK, if not just let me know and I will clarify. I also need to think about a team being split over multiple events, but I can deal with that later.

回答1:

Try this formula:

=IF(VLOOKUP(A2,Sheet1!$A$2:$E$4,3,FALSE)>0,"Event1",
IF(VLOOKUP(A2,Sheet1!$A$2:$E$4,4,FALSE)>0,"Event2","Event3"))