two table lookup match

[Guest]

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 | ? |

 

[Pete_UK]

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

 

[Guest]

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]

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

 

[Guest]

I really appreciate your help....

the H column spans down to row 623 (meaning 600+ territories)
the C and D columns span down to row 3088 (C column can contain 50+ of the
same county)

Thank you!

 

[Guest]

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

 

[Pete_UK]

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

 

[Guest]

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]

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

 

[Guest]

Thank you so much... It works!

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

 

[Pete_UK]

It would be easier to build up a new table in another worksheet, so
Insert | Worksheet and name it something like "Ref_data". To help line
cells up, change the background colour of cell H2 in your main sheet to
yellow, then highlight H2:H630 (or whatever), click <copy> then click
into B1 of the Ref_data sheet and click <paste>, then press <end> once
followed by <down-arrow> to take you to the bottom of that block and
move into the next empty cell and click <paste> again. Keep doing this
until you have 6 copies of the Territories down column B. Press
CTRL-Home to take you to A1 in this sheet.

Then select the main sheet and highlight i2:i630, click <copy>, select
the Ref_data sheet and <paste> this data at cell A1. Press <end>
followed by <down-arrow> and move your cursor into the cell in column A
next to the yellow cell in column B. Select the main sheet again,
highlight cells j2:j630, select the Ref_data sheet and <paste>, then
move down to the next yellow cell. Keep doing this until you have
copied the six columns of data up to N in the main sheet, ensuring that
the cursor in the Ref_data sheet is aligned with the yellow cell in
column B before you paste.

You may well have some missing entries in column A, as you will not
always have 6 counties for each territory. Highlight the cells A1:B3780
(or whatever) and use Data | Sort without a header using column A as
the sort key, and the blanks should drop to the bottom in a block -
locate these, highlight the rows and delete them - let's assume that
you are now left with 3200 rows. You could highlight column B at this
stage and select "No Fill" for the colour to get rid of the yellow
cells.

You now have a lookup table with the counties in Column A and the
Territories in Column B, so in D2 of your main sheet you could just
have the formula:

=VLOOKUP(C2,Ref_data!A$1:B$3200,2,0)

to return the Territory.

You could delete your Table 2 (H2:N630) in the main sheet if you wish.

Hope this helps.

Pete

 

[Guest]

Didn't even think of that....

but, if we need to add territories / systems this would have to be done each
time. I like the sum product for this reason. one name range and im set.

Thanks again.

 

[Pete_UK]

If you have another territory with, say, 5 counties, you just insert 5
rows in the middle of the range A1:B3200 somewhere then type 5 counties
in A and the Territory 5 times in B - the formulae will automatically
adjust and the data does not need to be sorted.

Anyway, you seem to have a number of alternatives now, so hopefully you
can crack on with your project.

Pete