I have two columns A1-A51 contains 4 Region names, B1-b51 contains the states that relate to the region, I would like to extract the states in to a separate column that match the region, so for example: if column C1 = West
I want Column D to contain all of the states that pertain to West
Col A Col B Col C Desired Results Col D
West OR West OR
West WA WA
West CA CA
Central TX
Central OK
Central MN
etc...
found this formula, but doesn't work and yes I am entering with CTRL,SHIFT,ENTER
{=INDEX($A$1:$B$67,SMALL(IF($A$1:$A$6=$C$1,ROW($A$1:$A$67)),ROW(1:1)))}
I am open for suggestions, seems simple enough, but can not figure out why the above will not work.
I want Column D to contain all of the states that pertain to West
Col A Col B Col C Desired Results Col D
West OR West OR
West WA WA
West CA CA
Central TX
Central OK
Central MN
etc...
found this formula, but doesn't work and yes I am entering with CTRL,SHIFT,ENTER
{=INDEX($A$1:$B$67,SMALL(IF($A$1:$A$6=$C$1,ROW($A$1:$A$67)),ROW(1:1)))}
I am open for suggestions, seems simple enough, but can not figure out why the above will not work.