VLOOKUP or ???

[imported_unknown]

I need a formula to do the following:

Column A Column B Column C
NAME TEAM # STATE
Fred Team 1 California
Fred Team 2 Louisiana
Wilma Team 1 Colorado
Scooby Team 2 New Jersey

I want to do this:

Column A Column B Column C
(INPUT)Name Team 1(FORMULA)Team 2 (FORMULA)

I tried a "IF" statement with an "AND" statement and a VLOOKUP with the two criteria, but I don't get the correct info.

 

[Brian from Maui]

Looks complicated but try Aladin's example

http://www.mrexcel.com/board/viewtopic.php?topic=16933&forum=2

 

[BigC]

Have you thought about re-arranging your data table? If each person has a maximum of two (2) teams (i.e. team 1, team 2), could you not set up your table as follows:

Name, Team 1 State, Team 2 State
Fred, California, Louisiana
Wilma, Colorado, blank
Scooby, blank, New Jersey

Now your vlookup would work by using different column numbers as the third argument in the vlookup formula for team 1 & team 2 respectively.
Alternatively, with the same table setup, use autofilters on the list to select the name required, and the matching record, with teams, will be displayed - no formulae required at all!

HTH
BigC