VLookup with Multiple Criteria

I've tried figuring this on my own but it is about a half step too advanced for my current working knowledge. I have a table that details which Agent should be selected for a particular city, and what the secondary Agent would be. So the information I want displayed is:

City: ATL
Primary Agent: Atlanta's Best
Secondary Agent: Dunghill Farmers

The sheet the information is stored in is organized as follows:
Column A: City
Column B: Agent Name
Column C: Rank (1, 2 or 3)

So what I need is:

When I type ATL into Cell A2, I need Cell B2 to return the Agent ranked 1, and Cell C2 to return the Agent ranked 2.

I think I need VLookup for this, but I can't figure out how to use it with 2 criteria, meaning I need to check the City the Agent is coded for (ATL) and the Ranking (1) to return Atlanta's Best into cell B2.

See attached for further detail. Thanks!

Originally Posted by Exodio

I've tried figuring this on my own but it is about a half step too advanced for my current working knowledge. I have a table that details which Agent should be selected for a particular city, and what the secondary Agent would be. So the information I want displayed is:

City: ATL
Primary Agent: Atlanta's Best
Secondary Agent: Dunghill Farmers

The sheet the information is stored in is organized as follows:
Column A: City
Column B: Agent Name
Column C: Rank (1, 2 or 3)

So what I need is:

When I type ATL into Cell A2, I need Cell B2 to return the Agent ranked 1, and Cell C2 to return the Agent ranked 2.

I think I need VLookup for this, but I can't figure out how to use it with 2 criteria, meaning I need to check the City the Agent is coded for (ATL) and the Ranking (1) to return Atlanta's Best into cell B2.

See attached for further detail. Thanks!

Try this:
In sheet Cities cell B2 enter the formula like this:

=IF(COLUMNS($B2:B2)>COUNTIF(Agents!$A$2:$A$7,$A2),"",INDEX(Agents!$B$2:$B$7,SMALL(IF(Agents!$A$2:$A$7=$A2,ROW(Agents!$B$2:$B$7)-ROW(Agents!$B$2)+1),COLUMNS(Cities!$B2:B2))))

Ctrl+Shift+Enter not just enter > Copy across then down.

Hope this helps.

Originally Posted by Exodio

I've tried figuring this on my own but it is about a half step too advanced for my current working knowledge. I have a table that details which Agent should be selected for a particular city, and what the secondary Agent would be. So the information I want displayed is:

City: ATL
Primary Agent: Atlanta's Best
Secondary Agent: Dunghill Farmers

The sheet the information is stored in is organized as follows:
Column A: City
Column B: Agent Name
Column C: Rank (1, 2 or 3)

So what I need is:

When I type ATL into Cell A2, I need Cell B2 to return the Agent ranked 1, and Cell C2 to return the Agent ranked 2.

I think I need VLookup for this, but I can't figure out how to use it with 2 criteria, meaning I need to check the City the Agent is coded for (ATL) and the Ranking (1) to return Atlanta's Best into cell B2.

See attached for further detail. Thanks!

See the attached

Mama - that is exactly what I am looking for. I will try and decipher what you did.
snasui - I will look into your formula also, thanks for the reply.