Return column header as value

[randraines]

Any help is greatly appreciated.
I have attached a sample of the sheet I am working on. I have a list of sales locations that i am trying to organize into region name. The Store's are in column A and the three regions with the stores listed under them in columns H, I, J. I am trying to take the value in column A, compare it to the table in H, I, J and return the region (header) in column B. I have been researching array formulas but i can't find one that returns the header. Please help.

[pjwhitfield]

Please Login or Register  to view this content.

[martindwilson]

try in b2 filled down
=INDEX($H$2:$J$2,SUMPRODUCT(($H$3:$J$8=A2)*COLUMN($A:$C))) assuming names are not duplicated
in $H$3:$J$8

[AlKey]

Try this array formula

=INDEX($H$2:$J$2,SMALL(IF($H$3:$J$8=A2,COLUMN($H$2:$J$2)-MIN(COLUMN($H$2:$J$2))+1),1))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[randraines]

thank you both for your replies. martindwilson, works perfectly. the other one probably works, but didn't try it.

[randraines]

martindwilson and AlKey, both formulas work great on the sample sheet. however, when i try to put them in my original sheet they don't work. i found a difference between the cities that on the left with the cities in the table to the right. when i copied one of the cities from the right and pasted it over the same city on the left, the formula worked. i copied the formatting from the cities on the right to the left for the rest of the cities but it didn't work. unfortunately, i'm not able to post the spreadsheet i am working on. any suggestions?

[martindwilson]

just copy cities list and cities table to another workbook attach that
that's all we need to see (hopefully)