Thank you so much... It works!

Also, how could i restructure the data so that I can apply a vlookup?

"Pete_UK" wrote:

This is the formula that PapaDos gave you on your other posting:

=INDEX($H$2:$H$4,SUMPRODUCT(($I$2:$N$4=C2)*ROW($H$ 2:$H$4))-ROW($H$2:$H$4)+1)

I've adjusted it to suit your sample file and copied it down and it
works, despite you thinking that SUMPRODUCT would not be suitable. Copy
the formula as it is into D2, and wherever there is a $4 you can change
this to $630 (or whatever, I can't remember the actual number of rows
you said you had).

The formula returns #VALUE if you have a county which does not exist in
Table2.

Another approach would have been to re-structure your data, as Dave
suggested in your other thread - keep checking that out, as other
responses are being added to it.

Hope this helps.

Pete


IntricateFool wrote:
You are the man.....

Thank you... and yes it is only a sample, but it should give the idea. I
will be able to figure it out from there.

I have tried several ways myself, just can't get it to search through all of
the H-N columns to obtain a match... It's killing me. Seriously been trying
to figure this out since yesterday morning.



"Pete_UK" wrote:

I downloaded the file you referred to in your earlier posting, but that
only had 3 rows of data, so I imagined it was just a sample. I'll work
on that for now and then adjust it to suit your 600+ rows of data.

Pete

IntricateFool wrote:
Is there anywhere you would trust a file being posted, so you can see an
example?

"Pete_UK" wrote:

Yes, but what is the range of territories? I assume they start in H2,
but how far do they go down? I'd like to give you a formula which
directly relates to your sheet, so I need to know how far down your
data stretches in Table 2.

Pete

IntricateFool wrote:
In table 2 - some territories only have 1 county others have up to 6
counties. This has truly stumped me. I feel like it shouldn't be that
difficult....
I just can't think logically i guess.

"Pete_UK" wrote:

How many Territories do you have in Table 2, i.e. how many rows of data
does the match have to apply across? Presumably, you do not always have
6 Counties for each Territory?

Pete

IntricateFool wrote:
I started another question earlier regarding this topic, but i guess I did
not explain it properly. I know it has to be doable....

Ok, so I have 2 tables.

In the first one Column C contains county names, Column D (the column I am
trying to determine) will contain the name of a Territory.

In table 2 - Column H contains the names of Territories and proceeding the
"Territory Name" is all the counties that fall into that specific territory
(columns I-N). Each territory can only contain unique names.

What type of formula would I use to have the cells in Column D (territory)
determine from Column C (county) they fall into. In other words which
territory does the county fall into based on the columns H through I?

Anybody, please please help me....
Below is a really half-arse model. I could send a file, for a better
reference.

C D H I J N
County | Territory | Territory |Cnty1|Cnty2 | Cnty6

Bucks | ? | West | Harris | Palm| Bing
Tern | ? | SW | Cole | Sand| Lowe
Palm | ? | <-This should be "West" Territory
Ford | ? |