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.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Quote #[/TD]
[TD]Name[/TD]
[TD]Territory[/TD]
[TD][/TD]
[TD][/TD]
[TD]Prefix[/TD]
[TD]Name Options[/TD]
[TD]Territory Options[/TD]
[/TR]
[TR]
[TD]A13-1621[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A10[/TD]
[TD]Brad[/TD]
[TD]Michigan[/TD]
[/TR]
[TR]
[TD]A10-1618[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A11[/TD]
[TD]Andrew[/TD]
[TD]Ohio[/TD]
[/TR]
[TR]
[TD]A21-4820[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A12[/TD]
[TD]Jim[/TD]
[TD]Illinois[/TD]
[/TR]
[TR]
[TD]A11-1652[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A13[/TD]
[TD]Jon[/TD]
[TD]Kentucky[/TD]
[/TR]
[TR]
[TD]A15-1525[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A14[/TD]
[TD]Blake[/TD]
[TD]Tennessee[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A15[/TD]
[TD]Tim[/TD]
[TD]Wisconsin[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Quote #[/TD]
[TD]Name[/TD]
[TD]Territory[/TD]
[TD][/TD]
[TD][/TD]
[TD]Prefix[/TD]
[TD]Name Options[/TD]
[TD]Territory Options[/TD]
[/TR]
[TR]
[TD]A13-1621[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A10[/TD]
[TD]Brad[/TD]
[TD]Michigan[/TD]
[/TR]
[TR]
[TD]A10-1618[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A11[/TD]
[TD]Andrew[/TD]
[TD]Ohio[/TD]
[/TR]
[TR]
[TD]A21-4820[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A12[/TD]
[TD]Jim[/TD]
[TD]Illinois[/TD]
[/TR]
[TR]
[TD]A11-1652[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A13[/TD]
[TD]Jon[/TD]
[TD]Kentucky[/TD]
[/TR]
[TR]
[TD]A15-1525[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A14[/TD]
[TD]Blake[/TD]
[TD]Tennessee[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A15[/TD]
[TD]Tim[/TD]
[TD]Wisconsin[/TD]
[/TR]
</tbody>[/TABLE]

All help would be appreciated. Thanks!