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

thp510

Board Regular
Joined
Oct 19, 2015
Messages
110
Office Version
  1. 365
Platform
  1. Windows
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?
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Maybe something like this.
Excel Workbook
ABCDEFG
1State#SMBState#ENT
2MT3JasonID14Jake
3CO14PhilMT4Kelly
4NV16PhilCO51Jessica
5AZ12JasonHI5Jessica
6NJ2LukeNJ24Jessica
7WA14PhilNM14Tom
8
9State#SMBState#ENT
10NY20LucasAK5Pam
11IL22JohnTX31Jacob
12TX9LucasTN52Kyle
13NC10JohnDC3Toni
14SC5HarryIL14Kyle
15GA0SueMD2Toni
16
17
18StateSegment
19COENTJessica
20GASMBSue
21ILENTKyle
22COSMBPhil
23GASMBSue
Sheet
 
Upvote 0
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?
 
Last edited:
Upvote 0
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
ABCDEFG
1State#SMBState#ENT
2MT3JasonID14Jake
3CO14PhilMT4Kelly
4NV16PhilCO51Jessica
5AZ12JasonHI5Jessica
6NJ2LukeNJ24Jessica
7WA14PhilNM14Tom
8
9State#SMBState#ENT
10NY20LucasAK5Pam
11IL22JohnTX31Jacob
12TX9LucasTN52Kyle
13NC10JohnDC3Toni
14SC5HarryIL14Kyle
15GA0SueMD2Toni
16
17
18StateSegment
19COENTJessica
20GASMBSue
21ILENTKyle
22COSMBPhil
23GASMBSue
Sheet
 
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top