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).
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]State[/TD]
[TD]#[/TD]
[TD]SMB[/TD]
[TD]
[/TD]
[TD]State[/TD]
[TD]#[/TD]
[TD]ENT[/TD]
[/TR]
[TR]
[TD]2 [/TD]
[TD]MT<style type="text/css"><!--br {mso-data-placement:same-cell;}--></style>[/TD]
[TD]3[/TD]
[TD]Jason[/TD]
[TD][/TD]
[TD]ID[/TD]
[TD]14[/TD]
[TD]Jake[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CO[/TD]
[TD]14[/TD]
[TD]Phil[/TD]
[TD][/TD]
[TD]MT[/TD]
[TD]4[/TD]
[TD]Kelly[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]NV[/TD]
[TD]16[/TD]
[TD]Phil[/TD]
[TD][/TD]
[TD]CO[/TD]
[TD]51[/TD]
[TD]Jessica[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]AZ[/TD]
[TD]12[/TD]
[TD]Jason[/TD]
[TD][/TD]
[TD]HI[/TD]
[TD]5[/TD]
[TD]Jessica[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]NJ[/TD]
[TD]2[/TD]
[TD]Luke[/TD]
[TD][/TD]
[TD]NJ[/TD]
[TD]24[/TD]
[TD]Jessica[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]WA[/TD]
[TD]14[/TD]
[TD]Phil[/TD]
[TD][/TD]
[TD]NM[/TD]
[TD]14[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]State[/TD]
[TD]#[/TD]
[TD]SMB[/TD]
[TD][/TD]
[TD]State[/TD]
[TD]#[/TD]
[TD]ENT[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]NY[/TD]
[TD]20[/TD]
[TD]Lucas[/TD]
[TD][/TD]
[TD]AK[/TD]
[TD]5[/TD]
[TD]Pam[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]IL[/TD]
[TD]22[/TD]
[TD]John[/TD]
[TD][/TD]
[TD]TX[/TD]
[TD]31[/TD]
[TD]Jacob[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]TX[/TD]
[TD]9[/TD]
[TD]Lucas[/TD]
[TD][/TD]
[TD]TN[/TD]
[TD]52[/TD]
[TD]Kyle[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]NC[/TD]
[TD]10[/TD]
[TD]John[/TD]
[TD][/TD]
[TD]DC[/TD]
[TD]3[/TD]
[TD]Toni[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]SC[/TD]
[TD]5[/TD]
[TD]Harry[/TD]
[TD][/TD]
[TD]IL[/TD]
[TD]14[/TD]
[TD]Kyle[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]GA[/TD]
[TD]0[/TD]
[TD]Sue[/TD]
[TD][/TD]
[TD]MD[/TD]
[TD]2[/TD]
[TD]Toni[/TD]
[/TR]
</tbody>[/TABLE]
For Worksheet_2, I have the following excel sheet that shows each state and sales segment.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]State[/TD]
[TD]Segment[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CO[/TD]
[TD]ENT[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]GA[/TD]
[TD]SMB[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]IL[/TD]
[TD]ENT[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]CO[/TD]
[TD]SMB[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]GA[/TD]
[TD]SMB[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]
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?
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]State[/TD]
[TD]#[/TD]
[TD]SMB[/TD]
[TD]
[/TD]
[TD]State[/TD]
[TD]#[/TD]
[TD]ENT[/TD]
[/TR]
[TR]
[TD]2 [/TD]
[TD]MT<style type="text/css"><!--br {mso-data-placement:same-cell;}--></style>[/TD]
[TD]3[/TD]
[TD]Jason[/TD]
[TD][/TD]
[TD]ID[/TD]
[TD]14[/TD]
[TD]Jake[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CO[/TD]
[TD]14[/TD]
[TD]Phil[/TD]
[TD][/TD]
[TD]MT[/TD]
[TD]4[/TD]
[TD]Kelly[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]NV[/TD]
[TD]16[/TD]
[TD]Phil[/TD]
[TD][/TD]
[TD]CO[/TD]
[TD]51[/TD]
[TD]Jessica[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]AZ[/TD]
[TD]12[/TD]
[TD]Jason[/TD]
[TD][/TD]
[TD]HI[/TD]
[TD]5[/TD]
[TD]Jessica[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]NJ[/TD]
[TD]2[/TD]
[TD]Luke[/TD]
[TD][/TD]
[TD]NJ[/TD]
[TD]24[/TD]
[TD]Jessica[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]WA[/TD]
[TD]14[/TD]
[TD]Phil[/TD]
[TD][/TD]
[TD]NM[/TD]
[TD]14[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]State[/TD]
[TD]#[/TD]
[TD]SMB[/TD]
[TD][/TD]
[TD]State[/TD]
[TD]#[/TD]
[TD]ENT[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]NY[/TD]
[TD]20[/TD]
[TD]Lucas[/TD]
[TD][/TD]
[TD]AK[/TD]
[TD]5[/TD]
[TD]Pam[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]IL[/TD]
[TD]22[/TD]
[TD]John[/TD]
[TD][/TD]
[TD]TX[/TD]
[TD]31[/TD]
[TD]Jacob[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]TX[/TD]
[TD]9[/TD]
[TD]Lucas[/TD]
[TD][/TD]
[TD]TN[/TD]
[TD]52[/TD]
[TD]Kyle[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]NC[/TD]
[TD]10[/TD]
[TD]John[/TD]
[TD][/TD]
[TD]DC[/TD]
[TD]3[/TD]
[TD]Toni[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]SC[/TD]
[TD]5[/TD]
[TD]Harry[/TD]
[TD][/TD]
[TD]IL[/TD]
[TD]14[/TD]
[TD]Kyle[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]GA[/TD]
[TD]0[/TD]
[TD]Sue[/TD]
[TD][/TD]
[TD]MD[/TD]
[TD]2[/TD]
[TD]Toni[/TD]
[/TR]
</tbody>[/TABLE]
For Worksheet_2, I have the following excel sheet that shows each state and sales segment.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]State[/TD]
[TD]Segment[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CO[/TD]
[TD]ENT[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]GA[/TD]
[TD]SMB[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]IL[/TD]
[TD]ENT[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]CO[/TD]
[TD]SMB[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]GA[/TD]
[TD]SMB[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]
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?
Last edited: