If a portion of a text string matches something in a list, return adjacent cells - FORMULA QUESTION

[andrewbhilty]

All,

I'm working on a portion of a spreadsheet where I'm trying to minimize the amount of input the user has to do. I would like a few cells to use the info in one cell (the Quote #) and return a variety of information.

The user inputs the quote number. I want the name and territory to fill automatically by referencing the master lists of quote prefixes, names, and territories.

See example below:
I was trying something like: =if(left(A3,3)=F3:F11,G3:G11,"")
Obviously doesn't work, but I wasn't sure how to have it search a list and return adjacent cells.

Quote #	Name	Territory			Prefix	Name Options	Territory Options
A13-1621					A10	Brad	Michigan
A10-1618					A11	Andrew	Ohio
A21-4820					A12	Jim	Illinois
A11-1652					A13	Jon	Kentucky
A15-1525					A14	Blake	Tennessee
					A15	Tim	Wisconsin

<tbody>
</tbody>

All help would be appreciated. Thanks!

 

[andrewbhilty]

FIGURED IT OUT! Used the =Vlookup function.

All,

I'm working on a portion of a spreadsheet where I'm trying to minimize the amount of input the user has to do. I would like a few cells to use the info in one cell (the Quote #) and return a variety of information.

The user inputs the quote number. I want the name and territory to fill automatically by referencing the master lists of quote prefixes, names, and territories.

See example below:
I was trying something like: =if(left(A3,3)=F3:F11,G3:G11,"")
Obviously doesn't work, but I wasn't sure how to have it search a list and return adjacent cells.

Quote #	Name	Territory			Prefix	Name Options	Territory Options
A13-1621					A10	Brad	Michigan
A10-1618					A11	Andrew	Ohio
A21-4820					A12	Jim	Illinois
A11-1652					A13	Jon	Kentucky
A15-1525					A14	Blake	Tennessee
					A15	Tim	Wisconsin

<tbody>
</tbody>

All help would be appreciated. Thanks!