help!

[nick]

I have 2 columns in sheet1, Division and Branch. Based on these two columns i
need to populate the state in a column "STATE". I have a table with branch n
divisions by which state could be derived. Detailed layout is below:

Sheet1

Branch Division STATE
041 0039 ?
041 0001 ?

Mapping Table:
Branch DIV State
041 0001 NY
041 0003 ATL
041 0008 FL
041 0010 CA
041 0015 NY
041 0021 NY
041 0023 NY
041 0024 NY
041 0026 MA
041 0027 PA
041 0030 MA
041 0031 MA
041 0038 MA
041 0039 ATL

So, i need a formula n my sheet1 which has a condition if sheet1branch=41
and div=39 then ATL etc (Conditions vary as per mapping table). i cant do
every value since there are a lot of records in the mapping table. Can anyone
tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS

[Tim M]

Your division #'s are not unique I take it? As in you can have a division
0001 in branch 41 and a division 0001 in branch 42 etc?

"nick" wrote:

> I have 2 columns in sheet1, Division and Branch. Based on these two columns i
> need to populate the state in a column "STATE". I have a table with branch n
> divisions by which state could be derived. Detailed layout is below:
>
> Sheet1
>
> Branch Division STATE
> 041 0039 ?
> 041 0001 ?
>
> Mapping Table:
> Branch DIV State
> 041 0001 NY
> 041 0003 ATL
> 041 0008 FL
> 041 0010 CA
> 041 0015 NY
> 041 0021 NY
> 041 0023 NY
> 041 0024 NY
> 041 0026 MA
> 041 0027 PA
> 041 0030 MA
> 041 0031 MA
> 041 0038 MA
> 041 0039 ATL
>
> So, i need a formula n my sheet1 which has a condition if sheet1branch=41
> and div=39 then ATL etc (Conditions vary as per mapping table). i cant do
> every value since there are a lot of records in the mapping table. Can anyone
> tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS

[nick]

Yes they are not unique...i have like 7-8 diff branches and one division
could belong to all 7 or just 1...forgot to mention that....sorry!

"Tim M" wrote:

> Your division #'s are not unique I take it? As in you can have a division
> 0001 in branch 41 and a division 0001 in branch 42 etc?
>
> "nick" wrote:
>
> > I have 2 columns in sheet1, Division and Branch. Based on these two columns i
> > need to populate the state in a column "STATE". I have a table with branch n
> > divisions by which state could be derived. Detailed layout is below:
> >
> > Sheet1
> >
> > Branch Division STATE
> > 041 0039 ?
> > 041 0001 ?
> >
> > Mapping Table:
> > Branch DIV State
> > 041 0001 NY
> > 041 0003 ATL
> > 041 0008 FL
> > 041 0010 CA
> > 041 0015 NY
> > 041 0021 NY
> > 041 0023 NY
> > 041 0024 NY
> > 041 0026 MA
> > 041 0027 PA
> > 041 0030 MA
> > 041 0031 MA
> > 041 0038 MA
> > 041 0039 ATL
> >
> > So, i need a formula n my sheet1 which has a condition if sheet1branch=41
> > and div=39 then ATL etc (Conditions vary as per mapping table). i cant do
> > every value since there are a lot of records in the mapping table. Can anyone
> > tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS

[CLR]

"nick" wrote:

> I have 2 columns in sheet1, Division and Branch. Based on these two columns i
> need to populate the state in a column "STATE". I have a table with branch n
> divisions by which state could be derived. Detailed layout is below:
>
> Sheet1
>
> Branch Division STATE
> 041 0039 ?
> 041 0001 ?
>
> Mapping Table:
> Branch DIV State
> 041 0001 NY
> 041 0003 ATL
> 041 0008 FL
> 041 0010 CA
> 041 0015 NY
> 041 0021 NY
> 041 0023 NY
> 041 0024 NY
> 041 0026 MA
> 041 0027 PA
> 041 0030 MA
> 041 0031 MA
> 041 0038 MA
> 041 0039 ATL
>
> So, i need a formula n my sheet1 which has a condition if sheet1branch=41
> and div=39 then ATL etc (Conditions vary as per mapping table). i cant do
> every value since there are a lot of records in the mapping table. Can anyone
> tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS

[CLR]

Oh well, "sometimes the Dragon wins"......sorry about the blank post.

What I meant to say is, I would recommend CONCATENATION. For your Table,
add a new column to it's left side with this formula in it and copied down
=B2&"_"&C2
And name the entire table with the new column included "StateTable"

Then, on Sheet1 in your STATES column, put this formula and copy down

=VLOOKUP(A2&"_"&B2,StateTable,4,FALSE)

hth
Vaya con Dios,
Chuck, CABGx3



"nick" wrote:

> I have 2 columns in sheet1, Division and Branch. Based on these two columns i
> need to populate the state in a column "STATE". I have a table with branch n
> divisions by which state could be derived. Detailed layout is below:
>
> Sheet1
>
> Branch Division STATE
> 041 0039 ?
> 041 0001 ?
>
> Mapping Table:
> Branch DIV State
> 041 0001 NY
> 041 0003 ATL
> 041 0008 FL
> 041 0010 CA
> 041 0015 NY
> 041 0021 NY
> 041 0023 NY
> 041 0024 NY
> 041 0026 MA
> 041 0027 PA
> 041 0030 MA
> 041 0031 MA
> 041 0038 MA
> 041 0039 ATL
>
> So, i need a formula n my sheet1 which has a condition if sheet1branch=41
> and div=39 then ATL etc (Conditions vary as per mapping table). i cant do
> every value since there are a lot of records in the mapping table. Can anyone
> tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS

[Tim M]

So Division 0001 in Branch 41 is NY
and you could have a Division 0001 in Branch 42 that is a different state
other than NY?

"nick" wrote:

> Yes they are not unique...i have like 7-8 diff branches and one division
> could belong to all 7 or just 1...forgot to mention that....sorry!
>
> "Tim M" wrote:
>
> > Your division #'s are not unique I take it? As in you can have a division
> > 0001 in branch 41 and a division 0001 in branch 42 etc?
> >
> > "nick" wrote:
> >
> > > I have 2 columns in sheet1, Division and Branch. Based on these two columns i
> > > need to populate the state in a column "STATE". I have a table with branch n
> > > divisions by which state could be derived. Detailed layout is below:
> > >
> > > Sheet1
> > >
> > > Branch Division STATE
> > > 041 0039 ?
> > > 041 0001 ?
> > >
> > > Mapping Table:
> > > Branch DIV State
> > > 041 0001 NY
> > > 041 0003 ATL
> > > 041 0008 FL
> > > 041 0010 CA
> > > 041 0015 NY
> > > 041 0021 NY
> > > 041 0023 NY
> > > 041 0024 NY
> > > 041 0026 MA
> > > 041 0027 PA
> > > 041 0030 MA
> > > 041 0031 MA
> > > 041 0038 MA
> > > 041 0039 ATL
> > >
> > > So, i need a formula n my sheet1 which has a condition if sheet1branch=41
> > > and div=39 then ATL etc (Conditions vary as per mapping table). i cant do
> > > every value since there are a lot of records in the mapping table. Can anyone
> > > tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS

[nick]

damn...i missed so much information...sorry about that. Yes the state could
vary as well...for example if branch 41 and div 0039 is NY, branch 42 div
0039 could be NY branch 45 div 0001 could be NYn branch 42 div 0001 could be
NY as well.

"Tim M" wrote:

> So Division 0001 in Branch 41 is NY
> and you could have a Division 0001 in Branch 42 that is a different state
> other than NY?
>
> "nick" wrote:
>
> > Yes they are not unique...i have like 7-8 diff branches and one division
> > could belong to all 7 or just 1...forgot to mention that....sorry!
> >
> > "Tim M" wrote:
> >
> > > Your division #'s are not unique I take it? As in you can have a division
> > > 0001 in branch 41 and a division 0001 in branch 42 etc?
> > >
> > > "nick" wrote:
> > >
> > > > I have 2 columns in sheet1, Division and Branch. Based on these two columns i
> > > > need to populate the state in a column "STATE". I have a table with branch n
> > > > divisions by which state could be derived. Detailed layout is below:
> > > >
> > > > Sheet1
> > > >
> > > > Branch Division STATE
> > > > 041 0039 ?
> > > > 041 0001 ?
> > > >
> > > > Mapping Table:
> > > > Branch DIV State
> > > > 041 0001 NY
> > > > 041 0003 ATL
> > > > 041 0008 FL
> > > > 041 0010 CA
> > > > 041 0015 NY
> > > > 041 0021 NY
> > > > 041 0023 NY
> > > > 041 0024 NY
> > > > 041 0026 MA
> > > > 041 0027 PA
> > > > 041 0030 MA
> > > > 041 0031 MA
> > > > 041 0038 MA
> > > > 041 0039 ATL
> > > >
> > > > So, i need a formula n my sheet1 which has a condition if sheet1branch=41
> > > > and div=39 then ATL etc (Conditions vary as per mapping table). i cant do
> > > > every value since there are a lot of records in the mapping table. Can anyone
> > > > tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS

[Dave Peterson]

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

nick wrote:
>
> I have 2 columns in sheet1, Division and Branch. Based on these two columns i
> need to populate the state in a column "STATE". I have a table with branch n
> divisions by which state could be derived. Detailed layout is below:
>
> Sheet1
>
> Branch Division STATE
> 041 0039 ?
> 041 0001 ?
>
> Mapping Table:
> Branch DIV State
> 041 0001 NY
> 041 0003 ATL
> 041 0008 FL
> 041 0010 CA
> 041 0015 NY
> 041 0021 NY
> 041 0023 NY
> 041 0024 NY
> 041 0026 MA
> 041 0027 PA
> 041 0030 MA
> 041 0031 MA
> 041 0038 MA
> 041 0039 ATL
>
> So, i need a formula n my sheet1 which has a condition if sheet1branch=41
> and div=39 then ATL etc (Conditions vary as per mapping table). i cant do
> every value since there are a lot of records in the mapping table. Can anyone
> tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS

--

Dave Peterson