Need help finding a result in a table (array? maybe simpler?)...

[thp510]

For Worksheet_1, I have the following table below across columns A to G and on rows 1 to 15 in excel. As you can see, it shows which person owns which segment (SMB, ENT, GROWTH, COM) based on their state. So for example the person that owns ENT in MT is Kelly (cell G3) or the person that owns SMB in NY is Lucas (cell C10). Multiple people can own the same state but not the same state and segment--that combination is unique (for example: both Phil and Jessica own CO, but Phil is an SMB person and Jessica is an ENT person).

	A	B	C	D	E	F	G
1	State	#	SMB		State	#	ENT
2	MT<style type="text/css"><!--br {mso-data-placement:same-cell;}--></style>	3	Jason		ID	14	Jake
3	CO	14	Phil		MT	4	Kelly
4	NV	16	Phil		CO	51	Jessica
5	AZ	12	Jason		HI	5	Jessica
6	NJ	2	Luke		NJ	24	Jessica
7	WA	14	Phil		NM	14	Tom
8
9	State	#	SMB		State	#	ENT
10	NY	20	Lucas		AK	5	Pam
11	IL	22	John		TX	31	Jacob
12	TX	9	Lucas		TN	52	Kyle
13	NC	10	John		DC	3	Toni
14	SC	5	Harry		IL	14	Kyle
15	GA	0	Sue		MD	2	Toni

<tbody>
</tbody>

For Worksheet_2, I have the following excel sheet that shows each state and sales segment.

	A	B	C
1	State	Segment	?
2	CO	ENT	?
3	GA	SMB	?
4	IL	ENT	?
5	CO	SMB	?
6	GA	SMB	?

<tbody>
</tbody>

I need to find a formula in Column C that can search in Worksheet_1 based on the State in Column A and Segment in Column B in Worksheet_1. So for example, in cell C2 (CO ENT) the results should be Jessica. For cell C6, it would be Sue.Any advice on a formula I can use in column C?

 

[AhoyNC]

Maybe something like this.

Excel Workbook
	A	B	C	D	E	F	G
1	State	#	SMB		State	#	ENT
2	MT	3	Jason		ID	14	Jake
3	CO	14	Phil		MT	4	Kelly
4	NV	16	Phil		CO	51	Jessica
5	AZ	12	Jason		HI	5	Jessica
6	NJ	2	Luke		NJ	24	Jessica
7	WA	14	Phil		NM	14	Tom
8
9	State	#	SMB		State	#	ENT
10	NY	20	Lucas		AK	5	Pam
11	IL	22	John		TX	31	Jacob
12	TX	9	Lucas		TN	52	Kyle
13	NC	10	John		DC	3	Toni
14	SC	5	Harry		IL	14	Kyle
15	GA	0	Sue		MD	2	Toni
16
17
18	State	Segment
19	CO	ENT	Jessica
20	GA	SMB	Sue
21	IL	ENT	Kyle
22	CO	SMB	Phil
23	GA	SMB	Sue
Sheet

 

[thp510]

Thanks Ahoy. However, my original is a small sample size so I actually have more states and segments (more than just ENT and SMB). This means I would have to make additional nested IF statements (at least 7 more). Is there a better way than to make a long IF statement?

 

[AhoyNC]

Try:
This formula assumes that the State column will always be 2 columns back from the Segment column.
This is also an array formula that must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

Excel Workbook
	A	B	C	D	E	F	G
1	State	#	SMB		State	#	ENT
2	MT	3	Jason		ID	14	Jake
3	CO	14	Phil		MT	4	Kelly
4	NV	16	Phil		CO	51	Jessica
5	AZ	12	Jason		HI	5	Jessica
6	NJ	2	Luke		NJ	24	Jessica
7	WA	14	Phil		NM	14	Tom
8
9	State	#	SMB		State	#	ENT
10	NY	20	Lucas		AK	5	Pam
11	IL	22	John		TX	31	Jacob
12	TX	9	Lucas		TN	52	Kyle
13	NC	10	John		DC	3	Toni
14	SC	5	Harry		IL	14	Kyle
15	GA	0	Sue		MD	2	Toni
16
17
18	State	Segment
19	CO	ENT	Jessica
20	GA	SMB	Sue
21	IL	ENT	Kyle
22	CO	SMB	Phil
23	GA	SMB	Sue
Sheet

 

[thp510]

Perfect! Thank you so much!!!

 

[Peter_SSs]

Perfect! Thank you so much!!!

Note that it does require that all instances of a segment heading are in the same column. That is, if the first "SMB" is in column C, then all "SMB" headings must be in column C, but that looks like it is the case for you anyway.

 

[AhoyNC]

You're welcome. Thanks for the feedback. As pointed out by Peter it also assumes that your header rows all be in the same order or the formula will fail.