How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

Difficult to see your layout, but...


If you want exact matches for just two columns (and return a value from a
third), you could use:

=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))

IntricateFool wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

--

Dave Peterson

Basically I want to have column D return what system it falls into based on
the county in column C. Each System contains anywhere from 2-6 counties.
Column C contains a county and over on another part of the spreadhseet I have
6 columns. The first columns contains the System Name, and the following
lists all the counties that fall under that system.

How would I go about displaying the system name in Column D based on the
county in Col C? There are about 600 systems to reference...

D H I J N
1 County|System|SysName|County1|County2|County6 |
2 | | | | |
|
3 Bucks| ? |System1 | Boman | Bucks | Farrel |

? should = "System1" looking through 600 systems to determine, and not on
same row like model above.
Some Systems only contain 2 or 3 counties, some contain up to 6.

Because "Bucks" falls into System1 based on J3 (Bucks falls under
"system1"), D3 would therefore return System1.
C contains about 1000 rows of counties, alot of them being the same. "Bucks"
could be listed 50 - 60 times in Column C, but only once within a SysName....
I need D to look through H:N and return the system based on the "County" in
column C.

I just can't seem to think that logically today...
Does that make more sense?
I appreciate your help!

"Dave Peterson" wrote:

Difficult to see your layout, but...


If you want exact matches for just two columns (and return a value from a
third), you could use:

=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))

IntricateFool wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

--

Dave Peterson

Did you try creating a formula based on that suggestion?



IntricateFool wrote:

Basically I want to have column D return what system it falls into based on
the county in column C. Each System contains anywhere from 2-6 counties.
Column C contains a county and over on another part of the spreadhseet I have
6 columns. The first columns contains the System Name, and the following
lists all the counties that fall under that system.

How would I go about displaying the system name in Column D based on the
county in Col C? There are about 600 systems to reference...

D H I J N
1 County|System|SysName|County1|County2|County6 |
2 | | | | |
|
3 Bucks| ? |System1 | Boman | Bucks | Farrel |

? should = "System1" looking through 600 systems to determine, and not on
same row like model above.
Some Systems only contain 2 or 3 counties, some contain up to 6.

Because "Bucks" falls into System1 based on J3 (Bucks falls under
"system1"), D3 would therefore return System1.
C contains about 1000 rows of counties, alot of them being the same. "Bucks"
could be listed 50 - 60 times in Column C, but only once within a SysName....
I need D to look through H:N and return the system based on the "County" in
column C.

I just can't seem to think that logically today...
Does that make more sense?
I appreciate your help!

"Dave Peterson" wrote:

Difficult to see your layout, but...


If you want exact matches for just two columns (and return a value from a
third), you could use:

=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))

IntricateFool wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

--

Dave Peterson


--

Dave Peterson

I could not follow what you had.

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


othersheet!$c$1:$c$100 is referring to the "county" column?
match(1, refers to?
match(1,(a2=

I'm not sure how I would apply that?


"Dave Peterson" wrote:

Did you try creating a formula based on that suggestion?



IntricateFool wrote:

Basically I want to have column D return what system it falls into based on
the county in column C. Each System contains anywhere from 2-6 counties.
Column C contains a county and over on another part of the spreadhseet I have
6 columns. The first columns contains the System Name, and the following
lists all the counties that fall under that system.

How would I go about displaying the system name in Column D based on the
county in Col C? There are about 600 systems to reference...

C D H I J N
1 County|System|SysName|County1|County2|County6 |
2 | | | | |
|
3 Bucks| ? |System1 | Boman | Bucks | Farrel |

? should = "System1" looking through 600 systems to determine, and not on
same row like model above.
Some Systems only contain 2 or 3 counties, some contain up to 6.

Because "Bucks" falls into System1 based on J3 (Bucks falls under
"system1"), D3 would therefore return System1.
C contains about 1000 rows of counties, alot of them being the same. "Bucks"
could be listed 50 - 60 times in Column C, but only once within a SysName....
I need D to look through H:N and return the system based on the "County" in
column C.

I just can't seem to think that logically today...
Does that make more sense?
I appreciate your help!

"Dave Peterson" wrote:

Difficult to see your layout, but...


If you want exact matches for just two columns (and return a value from a
third), you could use:

=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))

IntricateFool wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

--

Dave Peterson


--

Dave Peterson

And I still couldn't follow what you wanted.

If you had a table on another sheet (named OtherSheet) and wanted to use two
values to bring back a third, you could use that formula.

But I'm not sure that's the kind of thing you're looking for.



IntricateFool wrote:

I could not follow what you had.

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

othersheet!$c$1:$c$100 is referring to the "county" column?
match(1, refers to?
match(1,(a2=

I'm not sure how I would apply that?

"Dave Peterson" wrote:

Did you try creating a formula based on that suggestion?



IntricateFool wrote:

Basically I want to have column D return what system it falls into based on
the county in column C. Each System contains anywhere from 2-6 counties.
Column C contains a county and over on another part of the spreadhseet I have
6 columns. The first columns contains the System Name, and the following
lists all the counties that fall under that system.

How would I go about displaying the system name in Column D based on the
county in Col C? There are about 600 systems to reference...

C D H I J N
1 County|System|SysName|County1|County2|County6 |
2 | | | | |
|
3 Bucks| ? |System1 | Boman | Bucks | Farrel |

? should = "System1" looking through 600 systems to determine, and not on
same row like model above.
Some Systems only contain 2 or 3 counties, some contain up to 6.

Because "Bucks" falls into System1 based on J3 (Bucks falls under
"system1"), D3 would therefore return System1.
C contains about 1000 rows of counties, alot of them being the same. "Bucks"
could be listed 50 - 60 times in Column C, but only once within a SysName....
I need D to look through H:N and return the system based on the "County" in
column C.

I just can't seem to think that logically today...
Does that make more sense?
I appreciate your help!

"Dave Peterson" wrote:

Difficult to see your layout, but...


If you want exact matches for just two columns (and return a value from a
third), you could use:

=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))

IntricateFool wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Assuming your "System to County" table is in $Q$1:$V$5, enter this formula
into D2
=INDEX($Q$1:$Q$5, SUMPRODUCT(($R$1:$V$5 = D1) * ROW($Q$1:$Q$5)) -
ROW($Q$1:$Q$5) + 1)

Adjust the ranges to fit your needs...

NOTES:
The counties need to be unique in the table, or bad results will occur.

--
Festina Lente


"IntricateFool" wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

I think i am making it sound more complicate than what i need...

Ok, Column C will be the reference column for the lookup (contains the
"county" name), have the function look through a particular table, H1:N1000
(H column contains the system name and columns I - N contain the counties
within each system. I need column D to state the system name based on
whatever county is listed in column C.

C D H I J N
County | SysName |SysName1| Cnty1| Cnty2| Cnty6

I need to drag column D down so that it lists the SysName for each county
that it falls into. So if the county in cell C547 = "Fordham" , D547 all the
SysNames' counties until it finds "Fordham" and D547 will = whatever SysName
that Fordham county lies in.

Replace SysName with TerritoryName and it may make more sense....

Is there anywhere I can send a file? I think it may be much easier than I am
making it sound.


"Dave Peterson" wrote:

And I still couldn't follow what you wanted.

If you had a table on another sheet (named OtherSheet) and wanted to use two
values to bring back a third, you could use that formula.

But I'm not sure that's the kind of thing you're looking for.



IntricateFool wrote:

I could not follow what you had.

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

othersheet!$c$1:$c$100 is referring to the "county" column?
match(1, refers to?
match(1,(a2=

I'm not sure how I would apply that?

"Dave Peterson" wrote:

Did you try creating a formula based on that suggestion?



IntricateFool wrote:

Basically I want to have column D return what system it falls into based on
the county in column C. Each System contains anywhere from 2-6 counties.
Column C contains a county and over on another part of the spreadhseet I have
6 columns. The first columns contains the System Name, and the following
lists all the counties that fall under that system.

How would I go about displaying the system name in Column D based on the
county in Col C? There are about 600 systems to reference...

C D H I J N
1 County|System|SysName|County1|County2|County6 |
2 | | | | |
|
3 Bucks| ? |System1 | Boman | Bucks | Farrel |

? should = "System1" looking through 600 systems to determine, and not on
same row like model above.
Some Systems only contain 2 or 3 counties, some contain up to 6.

Because "Bucks" falls into System1 based on J3 (Bucks falls under
"system1"), D3 would therefore return System1.
C contains about 1000 rows of counties, alot of them being the same. "Bucks"
could be listed 50 - 60 times in Column C, but only once within a SysName....
I need D to look through H:N and return the system based on the "County" in
column C.

I just can't seem to think that logically today...
Does that make more sense?
I appreciate your help!

"Dave Peterson" wrote:

Difficult to see your layout, but...


If you want exact matches for just two columns (and return a value from a
third), you could use:

=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))

IntricateFool wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Oups !

Replace D1 by C2 in the above formula, sorry...
--
Festina Lente


"PapaDos" wrote:

Assuming your "System to County" table is in $Q$1:$V$5, enter this formula
into D2
=INDEX($Q$1:$Q$5, SUMPRODUCT(($R$1:$V$5 = D1) * ROW($Q$1:$Q$5)) -
ROW($Q$1:$Q$5) + 1)

Adjust the ranges to fit your needs...

NOTES:
The counties need to be unique in the table, or bad results will occur.

--
Festina Lente


"IntricateFool" wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

I have uploaded a sample of what I am trying to accomplish...
http://www.filefactory.com/file/c9d282/

Hope this makes it a bit easier...

Thanks again

"Dave Peterson" wrote:

And I still couldn't follow what you wanted.

If you had a table on another sheet (named OtherSheet) and wanted to use two
values to bring back a third, you could use that formula.

But I'm not sure that's the kind of thing you're looking for.



IntricateFool wrote:

I could not follow what you had.

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

othersheet!$c$1:$c$100 is referring to the "county" column?
match(1, refers to?
match(1,(a2=

I'm not sure how I would apply that?

"Dave Peterson" wrote:

Did you try creating a formula based on that suggestion?



IntricateFool wrote:

Basically I want to have column D return what system it falls into based on
the county in column C. Each System contains anywhere from 2-6 counties.
Column C contains a county and over on another part of the spreadhseet I have
6 columns. The first columns contains the System Name, and the following
lists all the counties that fall under that system.

How would I go about displaying the system name in Column D based on the
county in Col C? There are about 600 systems to reference...

C D H I J N
1 County|System|SysName|County1|County2|County6 |
2 | | | | |
|
3 Bucks| ? |System1 | Boman | Bucks | Farrel |

? should = "System1" looking through 600 systems to determine, and not on
same row like model above.
Some Systems only contain 2 or 3 counties, some contain up to 6.

Because "Bucks" falls into System1 based on J3 (Bucks falls under
"system1"), D3 would therefore return System1.
C contains about 1000 rows of counties, alot of them being the same. "Bucks"
could be listed 50 - 60 times in Column C, but only once within a SysName....
I need D to look through H:N and return the system based on the "County" in
column C.

I just can't seem to think that logically today...
Does that make more sense?
I appreciate your help!

"Dave Peterson" wrote:

Difficult to see your layout, but...


If you want exact matches for just two columns (and return a value from a
third), you could use:

=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))

IntricateFool wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Im sorry, if it takes you through a roundabout way to download the file, but
it is there....

"Dave Peterson" wrote:

And I still couldn't follow what you wanted.

If you had a table on another sheet (named OtherSheet) and wanted to use two
values to bring back a third, you could use that formula.

But I'm not sure that's the kind of thing you're looking for.



IntricateFool wrote:

I could not follow what you had.

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

othersheet!$c$1:$c$100 is referring to the "county" column?
match(1, refers to?
match(1,(a2=

I'm not sure how I would apply that?

"Dave Peterson" wrote:

Did you try creating a formula based on that suggestion?



IntricateFool wrote:

Basically I want to have column D return what system it falls into based on
the county in column C. Each System contains anywhere from 2-6 counties.
Column C contains a county and over on another part of the spreadhseet I have
6 columns. The first columns contains the System Name, and the following
lists all the counties that fall under that system.

How would I go about displaying the system name in Column D based on the
county in Col C? There are about 600 systems to reference...

C D H I J N
1 County|System|SysName|County1|County2|County6 |
2 | | | | |
|
3 Bucks| ? |System1 | Boman | Bucks | Farrel |

? should = "System1" looking through 600 systems to determine, and not on
same row like model above.
Some Systems only contain 2 or 3 counties, some contain up to 6.

Because "Bucks" falls into System1 based on J3 (Bucks falls under
"system1"), D3 would therefore return System1.
C contains about 1000 rows of counties, alot of them being the same. "Bucks"
could be listed 50 - 60 times in Column C, but only once within a SysName....
I need D to look through H:N and return the system based on the "County" in
column C.

I just can't seem to think that logically today...
Does that make more sense?
I appreciate your help!

"Dave Peterson" wrote:

Difficult to see your layout, but...


If you want exact matches for just two columns (and return a value from a
third), you could use:

=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))

IntricateFool wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I don't open attachments or files that are uploaded elsewhere.

Maybe PapaDos has the answer. I still don't understand.

IntricateFool wrote:

I have uploaded a sample of what I am trying to accomplish...
http://www.filefactory.com/file/c9d282/

Hope this makes it a bit easier...

Thanks again

"Dave Peterson" wrote:

And I still couldn't follow what you wanted.

If you had a table on another sheet (named OtherSheet) and wanted to use two
values to bring back a third, you could use that formula.

But I'm not sure that's the kind of thing you're looking for.



IntricateFool wrote:

I could not follow what you had.

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

othersheet!$c$1:$c$100 is referring to the "county" column?
match(1, refers to?
match(1,(a2=

I'm not sure how I would apply that?

"Dave Peterson" wrote:

Did you try creating a formula based on that suggestion?



IntricateFool wrote:

Basically I want to have column D return what system it falls into based on
the county in column C. Each System contains anywhere from 2-6 counties.
Column C contains a county and over on another part of the spreadhseet I have
6 columns. The first columns contains the System Name, and the following
lists all the counties that fall under that system.

How would I go about displaying the system name in Column D based on the
county in Col C? There are about 600 systems to reference...

C D H I J N
1 County|System|SysName|County1|County2|County6 |
2 | | | | |
|
3 Bucks| ? |System1 | Boman | Bucks | Farrel |

? should = "System1" looking through 600 systems to determine, and not on
same row like model above.
Some Systems only contain 2 or 3 counties, some contain up to 6.

Because "Bucks" falls into System1 based on J3 (Bucks falls under
"system1"), D3 would therefore return System1.
C contains about 1000 rows of counties, alot of them being the same. "Bucks"
could be listed 50 - 60 times in Column C, but only once within a SysName....
I need D to look through H:N and return the system based on the "County" in
column C.

I just can't seem to think that logically today...
Does that make more sense?
I appreciate your help!

"Dave Peterson" wrote:

Difficult to see your layout, but...


If you want exact matches for just two columns (and return a value from a
third), you could use:

=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))

IntricateFool wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Could you check out the file I have posted online and see if you can get it
working? It is not working for me... I have been trying all day.

http://dl019.filefactory.com/dl/f/c9...2fd2d5a671586/
Showing you an actualy file is the only way I can think of to show you
exactly what I am trying to do...

I really appreciate your help.

"PapaDos" wrote:

Oups !

Replace D1 by C2 in the above formula, sorry...
--
Festina Lente


"PapaDos" wrote:

Assuming your "System to County" table is in $Q$1:$V$5, enter this formula
into D2
=INDEX($Q$1:$Q$5, SUMPRODUCT(($R$1:$V$5 = D1) * ROW($Q$1:$Q$5)) -
ROW($Q$1:$Q$5) + 1)

Adjust the ranges to fit your needs...

NOTES:
The counties need to be unique in the table, or bad results will occur.

--
Festina Lente


"IntricateFool" wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

Sorry, I never open files coming from that kind of source.

Did you try to adjust my formula t your ranges ?
--
Festina Lente


"IntricateFool" wrote:

Could you check out the file I have posted online and see if you can get it
working? It is not working for me... I have been trying all day.

http://dl019.filefactory.com/dl/f/c9...2fd2d5a671586/
Showing you an actualy file is the only way I can think of to show you
exactly what I am trying to do...

I really appreciate your help.

"PapaDos" wrote:

Oups !

Replace D1 by C2 in the above formula, sorry...
--
Festina Lente


"PapaDos" wrote:

Assuming your "System to County" table is in $Q$1:$V$5, enter this formula
into D2
=INDEX($Q$1:$Q$5, SUMPRODUCT(($R$1:$V$5 = D1) * ROW($Q$1:$Q$5)) -
ROW($Q$1:$Q$5) + 1)

Adjust the ranges to fit your needs...

NOTES:
The counties need to be unique in the table, or bad results will occur.

--
Festina Lente


"IntricateFool" wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

Yes I tried, but it is not working. I don't see how using the sumproduct
function would work. I am thinking it would be more of a combination of index
and match. Then again I could be completely wrong.

Is there anywhere else I could forward the file?

"PapaDos" wrote:

Sorry, I never open files coming from that kind of source.

Did you try to adjust my formula t your ranges ?
--
Festina Lente


"IntricateFool" wrote:

Could you check out the file I have posted online and see if you can get it
working? It is not working for me... I have been trying all day.

http://dl019.filefactory.com/dl/f/c9...2fd2d5a671586/
Showing you an actualy file is the only way I can think of to show you
exactly what I am trying to do...

I really appreciate your help.

"PapaDos" wrote:

Oups !

Replace D1 by C2 in the above formula, sorry...
--
Festina Lente


"PapaDos" wrote:

Assuming your "System to County" table is in $Q$1:$V$5, enter this formula
into D2
=INDEX($Q$1:$Q$5, SUMPRODUCT(($R$1:$V$5 = D1) * ROW($Q$1:$Q$5)) -
ROW($Q$1:$Q$5) + 1)

Adjust the ranges to fit your needs...

NOTES:
The counties need to be unique in the table, or bad results will occur.

--
Festina Lente


"IntricateFool" wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

I know you have the answer, you always do.
I must not be explaining this properly.
Is there anywhere you would trust opening a file?

All I want to do is have one function to determine which county in column C
falls under which of the "Territories" in column H based on each Territories
counties proceding in I through N, then return the name of the Territory in
column D.

I have been searching for a tutorial or example of this all over the net,
but dont know exactly what to search for....

Please help.

"Dave Peterson" wrote:

I don't open attachments or files that are uploaded elsewhere.

Maybe PapaDos has the answer. I still don't understand.

IntricateFool wrote:

I have uploaded a sample of what I am trying to accomplish...
http://www.filefactory.com/file/c9d282/

Hope this makes it a bit easier...

Thanks again

"Dave Peterson" wrote:

And I still couldn't follow what you wanted.

If you had a table on another sheet (named OtherSheet) and wanted to use two
values to bring back a third, you could use that formula.

But I'm not sure that's the kind of thing you're looking for.



IntricateFool wrote:

I could not follow what you had.

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

othersheet!$c$1:$c$100 is referring to the "county" column?
match(1, refers to?
match(1,(a2=

I'm not sure how I would apply that?

"Dave Peterson" wrote:

Did you try creating a formula based on that suggestion?



IntricateFool wrote:

Basically I want to have column D return what system it falls into based on
the county in column C. Each System contains anywhere from 2-6 counties.
Column C contains a county and over on another part of the spreadhseet I have
6 columns. The first columns contains the System Name, and the following
lists all the counties that fall under that system.

How would I go about displaying the system name in Column D based on the
county in Col C? There are about 600 systems to reference...

C D H I J N
1 County|System|SysName|County1|County2|County6 |
2 | | | | |
|
3 Bucks| ? |System1 | Boman | Bucks | Farrel |

? should = "System1" looking through 600 systems to determine, and not on
same row like model above.
Some Systems only contain 2 or 3 counties, some contain up to 6.

Because "Bucks" falls into System1 based on J3 (Bucks falls under
"system1"), D3 would therefore return System1.
C contains about 1000 rows of counties, alot of them being the same. "Bucks"
could be listed 50 - 60 times in Column C, but only once within a SysName....
I need D to look through H:N and return the system based on the "County" in
column C.

I just can't seem to think that logically today...
Does that make more sense?
I appreciate your help!

"Dave Peterson" wrote:

Difficult to see your layout, but...


If you want exact matches for just two columns (and return a value from a
third), you could use:

=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))

IntricateFool wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Maybe you could change the layout of your table to make it easier.

I'd put the key column (territories) in column A of a different sheet.

Then put the county associated with that territory in column B of that same
sheet.

Then I could use =vlookup() to return the county for that territory.

See Debra Dalgleish's site for instructions on =vlookup().

IntricateFool wrote:

I know you have the answer, you always do.
I must not be explaining this properly.
Is there anywhere you would trust opening a file?

All I want to do is have one function to determine which county in column C
falls under which of the "Territories" in column H based on each Territories
counties proceding in I through N, then return the name of the Territory in
column D.

I have been searching for a tutorial or example of this all over the net,
but dont know exactly what to search for....

Please help.

"Dave Peterson" wrote:

I don't open attachments or files that are uploaded elsewhere.

Maybe PapaDos has the answer. I still don't understand.

IntricateFool wrote:

I have uploaded a sample of what I am trying to accomplish...
http://www.filefactory.com/file/c9d282/

Hope this makes it a bit easier...

Thanks again

"Dave Peterson" wrote:

And I still couldn't follow what you wanted.

If you had a table on another sheet (named OtherSheet) and wanted to use two
values to bring back a third, you could use that formula.

But I'm not sure that's the kind of thing you're looking for.



IntricateFool wrote:

I could not follow what you had.

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

othersheet!$c$1:$c$100 is referring to the "county" column?
match(1, refers to?
match(1,(a2=

I'm not sure how I would apply that?

"Dave Peterson" wrote:

Did you try creating a formula based on that suggestion?



IntricateFool wrote:

Basically I want to have column D return what system it falls into based on
the county in column C. Each System contains anywhere from 2-6 counties.
Column C contains a county and over on another part of the spreadhseet I have
6 columns. The first columns contains the System Name, and the following
lists all the counties that fall under that system.

How would I go about displaying the system name in Column D based on the
county in Col C? There are about 600 systems to reference...

C D H I J N
1 County|System|SysName|County1|County2|County6 |
2 | | | | |
|
3 Bucks| ? |System1 | Boman | Bucks | Farrel |

? should = "System1" looking through 600 systems to determine, and not on
same row like model above.
Some Systems only contain 2 or 3 counties, some contain up to 6.

Because "Bucks" falls into System1 based on J3 (Bucks falls under
"system1"), D3 would therefore return System1.
C contains about 1000 rows of counties, alot of them being the same. "Bucks"
could be listed 50 - 60 times in Column C, but only once within a SysName....
I need D to look through H:N and return the system based on the "County" in
column C.

I just can't seem to think that logically today...
Does that make more sense?
I appreciate your help!

"Dave Peterson" wrote:

Difficult to see your layout, but...


If you want exact matches for just two columns (and return a value from a
third), you could use:

=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))

IntricateFool wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Send a copy of the file to me. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious. Include a detailed explanation of what
you're trying to do.

Biff

"IntricateFool" wrote in message
...
Yes I tried, but it is not working. I don't see how using the sumproduct
function would work. I am thinking it would be more of a combination of
index
and match. Then again I could be completely wrong.

Is there anywhere else I could forward the file?

"PapaDos" wrote:

Sorry, I never open files coming from that kind of source.

Did you try to adjust my formula t your ranges ?
--
Festina Lente


"IntricateFool" wrote:

Could you check out the file I have posted online and see if you can
get it
working? It is not working for me... I have been trying all day.

http://dl019.filefactory.com/dl/f/c9...2fd2d5a671586/
Showing you an actualy file is the only way I can think of to show you
exactly what I am trying to do...

I really appreciate your help.

"PapaDos" wrote:

Oups !

Replace D1 by C2 in the above formula, sorry...
--
Festina Lente


"PapaDos" wrote:

Assuming your "System to County" table is in $Q$1:$V$5, enter this
formula
into D2
=INDEX($Q$1:$Q$5, SUMPRODUCT(($R$1:$V$5 = D1) * ROW($Q$1:$Q$5)) -
ROW($Q$1:$Q$5) + 1)

Adjust the ranges to fit your needs...

NOTES:
The counties need to be unique in the table, or bad results will
occur.

--
Festina Lente


"IntricateFool" wrote:

How would I go about having a function return a single value from
searching
several different columns and matching the proper "name" with its
"county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2
County3
2 Mobile ? | System 1 Mobile
Montco
Harris
3 Bucks ? | System 2 Ford
George
Newman
4 George System2 | System 3 Boman Bucks
Farrel
5 York ? | System 4 Rosel
Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I
need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely
off?
obviously not working...
Any help would be greatly appreciated...

I use Vlookup like it is second nature....

The thing is each territory can have between 1 and 6 counties associated to
it.

I wish i could use vlookup....

Is there anywhere you would trust that I can send a file to? If you see it
in a spreadsheet you will see exactly what it is I am trying to do. I just
don't know how to explain it any better.

I appreciate your help. You probably are getting as frustrated as I am.

"Dave Peterson" wrote:

Maybe you could change the layout of your table to make it easier.

I'd put the key column (territories) in column A of a different sheet.

Then put the county associated with that territory in column B of that same
sheet.

Then I could use =vlookup() to return the county for that territory.

See Debra Dalgleish's site for instructions on =vlookup().

IntricateFool wrote:

I know you have the answer, you always do.
I must not be explaining this properly.
Is there anywhere you would trust opening a file?

All I want to do is have one function to determine which county in column C
falls under which of the "Territories" in column H based on each Territories
counties proceding in I through N, then return the name of the Territory in
column D.

I have been searching for a tutorial or example of this all over the net,
but dont know exactly what to search for....

Please help.

"Dave Peterson" wrote:

I don't open attachments or files that are uploaded elsewhere.

Maybe PapaDos has the answer. I still don't understand.

IntricateFool wrote:

I have uploaded a sample of what I am trying to accomplish...
http://www.filefactory.com/file/c9d282/

Hope this makes it a bit easier...

Thanks again

"Dave Peterson" wrote:

And I still couldn't follow what you wanted.

If you had a table on another sheet (named OtherSheet) and wanted to use two
values to bring back a third, you could use that formula.

But I'm not sure that's the kind of thing you're looking for.



IntricateFool wrote:

I could not follow what you had.

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

othersheet!$c$1:$c$100 is referring to the "county" column?
match(1, refers to?
match(1,(a2=

I'm not sure how I would apply that?

"Dave Peterson" wrote:

Did you try creating a formula based on that suggestion?



IntricateFool wrote:

Basically I want to have column D return what system it falls into based on
the county in column C. Each System contains anywhere from 2-6 counties.
Column C contains a county and over on another part of the spreadhseet I have
6 columns. The first columns contains the System Name, and the following
lists all the counties that fall under that system.

How would I go about displaying the system name in Column D based on the
county in Col C? There are about 600 systems to reference...

C D H I J N
1 County|System|SysName|County1|County2|County6 |
2 | | | | |
|
3 Bucks| ? |System1 | Boman | Bucks | Farrel |

? should = "System1" looking through 600 systems to determine, and not on
same row like model above.
Some Systems only contain 2 or 3 counties, some contain up to 6.

Because "Bucks" falls into System1 based on J3 (Bucks falls under
"system1"), D3 would therefore return System1.
C contains about 1000 rows of counties, alot of them being the same. "Bucks"
could be listed 50 - 60 times in Column C, but only once within a SysName....
I need D to look through H:N and return the system based on the "County" in
column C.

I just can't seem to think that logically today...
Does that make more sense?
I appreciate your help!

"Dave Peterson" wrote:

Difficult to see your layout, but...


If you want exact matches for just two columns (and return a value from a
third), you could use:

=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))

IntricateFool wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I'm not sure a workbook would help. I still don't understand the problem.

IntricateFool wrote:

I use Vlookup like it is second nature....

The thing is each territory can have between 1 and 6 counties associated to
it.

I wish i could use vlookup....

Is there anywhere you would trust that I can send a file to? If you see it
in a spreadsheet you will see exactly what it is I am trying to do. I just
don't know how to explain it any better.

I appreciate your help. You probably are getting as frustrated as I am.

"Dave Peterson" wrote:

Maybe you could change the layout of your table to make it easier.

I'd put the key column (territories) in column A of a different sheet.

Then put the county associated with that territory in column B of that same
sheet.

Then I could use =vlookup() to return the county for that territory.

See Debra Dalgleish's site for instructions on =vlookup().

IntricateFool wrote:

I know you have the answer, you always do.
I must not be explaining this properly.
Is there anywhere you would trust opening a file?

All I want to do is have one function to determine which county in column C
falls under which of the "Territories" in column H based on each Territories
counties proceding in I through N, then return the name of the Territory in
column D.

I have been searching for a tutorial or example of this all over the net,
but dont know exactly what to search for....

Please help.

"Dave Peterson" wrote:

I don't open attachments or files that are uploaded elsewhere.

Maybe PapaDos has the answer. I still don't understand.

IntricateFool wrote:

I have uploaded a sample of what I am trying to accomplish...
http://www.filefactory.com/file/c9d282/

Hope this makes it a bit easier...

Thanks again

"Dave Peterson" wrote:

And I still couldn't follow what you wanted.

If you had a table on another sheet (named OtherSheet) and wanted to use two
values to bring back a third, you could use that formula.

But I'm not sure that's the kind of thing you're looking for.



IntricateFool wrote:

I could not follow what you had.

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

othersheet!$c$1:$c$100 is referring to the "county" column?
match(1, refers to?
match(1,(a2=

I'm not sure how I would apply that?

"Dave Peterson" wrote:

Did you try creating a formula based on that suggestion?



IntricateFool wrote:

Basically I want to have column D return what system it falls into based on
the county in column C. Each System contains anywhere from 2-6 counties.
Column C contains a county and over on another part of the spreadhseet I have
6 columns. The first columns contains the System Name, and the following
lists all the counties that fall under that system.

How would I go about displaying the system name in Column D based on the
county in Col C? There are about 600 systems to reference...

C D H I J N
1 County|System|SysName|County1|County2|County6 |
2 | | | | |
|
3 Bucks| ? |System1 | Boman | Bucks | Farrel |

? should = "System1" looking through 600 systems to determine, and not on
same row like model above.
Some Systems only contain 2 or 3 counties, some contain up to 6.

Because "Bucks" falls into System1 based on J3 (Bucks falls under
"system1"), D3 would therefore return System1.
C contains about 1000 rows of counties, alot of them being the same. "Bucks"
could be listed 50 - 60 times in Column C, but only once within a SysName....
I need D to look through H:N and return the system based on the "County" in
column C.

I just can't seem to think that logically today...
Does that make more sense?
I appreciate your help!

"Dave Peterson" wrote:

Difficult to see your layout, but...


If you want exact matches for just two columns (and return a value from a
third), you could use:

=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))

IntricateFool wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Just tell me the exact address of your whole table (Systems to counties) and
the addresses of the cells containing the county to search and where you want
the result...
--
Festina Lente


"IntricateFool" wrote:

Yes I tried, but it is not working. I don't see how using the sumproduct
function would work. I am thinking it would be more of a combination of index
and match. Then again I could be completely wrong.

Is there anywhere else I could forward the file?

"PapaDos" wrote:

Sorry, I never open files coming from that kind of source.

Did you try to adjust my formula t your ranges ?
--
Festina Lente


"IntricateFool" wrote:

Could you check out the file I have posted online and see if you can get it
working? It is not working for me... I have been trying all day.

http://dl019.filefactory.com/dl/f/c9...2fd2d5a671586/
Showing you an actualy file is the only way I can think of to show you
exactly what I am trying to do...

I really appreciate your help.

"PapaDos" wrote:

Oups !

Replace D1 by C2 in the above formula, sorry...
--
Festina Lente


"PapaDos" wrote:

Assuming your "System to County" table is in $Q$1:$V$5, enter this formula
into D2
=INDEX($Q$1:$Q$5, SUMPRODUCT(($R$1:$V$5 = D1) * ROW($Q$1:$Q$5)) -
ROW($Q$1:$Q$5) + 1)

Adjust the ranges to fit your needs...

NOTES:
The counties need to be unique in the table, or bad results will occur.

--
Festina Lente


"IntricateFool" wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

Update on this, as the OP started another thread.

I took the risk and downloaded the file. Your formula worked well once
I had adjusted it to suit the ranges in the file and made the
correction you pointed out. Here is the amended version:

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

In the sample file the data covers H2:N4, but in reality this will go
down to row 630+.

The formula produced #VALUE if the county in column C was not present
in the reference table.

Pete

PapaDos wrote:
Oups !

Replace D1 by C2 in the above formula, sorry...
--
Festina Lente


"PapaDos" wrote:

Assuming your "System to County" table is in $Q$1:$V$5, enter this formula
into D2
=INDEX($Q$1:$Q$5, SUMPRODUCT(($R$1:$V$5 = D1) * ROW($Q$1:$Q$5)) -
ROW($Q$1:$Q$5) + 1)

Adjust the ranges to fit your needs...

NOTES:
The counties need to be unique in the table, or bad results will occur.

--
Festina Lente


"IntricateFool" wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

Dude,

with some changes of your function and the help of Pete_UK I got it
working...

Really appreciate your help.

Thanks

in case you were wondering, I used:

=INDEX($H$3:$H$5,SUMPRODUCT(($I$3:$N$5=C3)*ROW($H$ 3:$H$5))-ROW($H$3:$H$5)+1)

I was getting the C3 all messed up.



"PapaDos" wrote:

Just tell me the exact address of your whole table (Systems to counties) and
the addresses of the cells containing the county to search and where you want
the result...
--
Festina Lente


"IntricateFool" wrote:

Yes I tried, but it is not working. I don't see how using the sumproduct
function would work. I am thinking it would be more of a combination of index
and match. Then again I could be completely wrong.

Is there anywhere else I could forward the file?

"PapaDos" wrote:

Sorry, I never open files coming from that kind of source.

Did you try to adjust my formula t your ranges ?
--
Festina Lente


"IntricateFool" wrote:

Could you check out the file I have posted online and see if you can get it
working? It is not working for me... I have been trying all day.

http://dl019.filefactory.com/dl/f/c9...2fd2d5a671586/
Showing you an actualy file is the only way I can think of to show you
exactly what I am trying to do...

I really appreciate your help.

"PapaDos" wrote:

Oups !

Replace D1 by C2 in the above formula, sorry...
--
Festina Lente


"PapaDos" wrote:

Assuming your "System to County" table is in $Q$1:$V$5, enter this formula
into D2
=INDEX($Q$1:$Q$5, SUMPRODUCT(($R$1:$V$5 = D1) * ROW($Q$1:$Q$5)) -
ROW($Q$1:$Q$5) + 1)

Adjust the ranges to fit your needs...

NOTES:
The counties need to be unique in the table, or bad results will occur.

--
Festina Lente


"IntricateFool" wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...

Thanks to both of you for the feedback...
--
Festina Lente


"IntricateFool" wrote:

How would I go about having a function return a single value from searching
several different columns and matching the proper "name" with its "county"?

Here is what the spreadsheet looks like:
C D | H I
J K
1 County System | System County1 County2 County3
2 Mobile ? | System 1 Mobile Montco
Harris
3 Bucks ? | System 2 Ford George
Newman
4 George System2 | System 3 Boman Bucks Farrel
5 York ? | System 4 Rosel Duke
York

D2 should = System 1
D3 should = System 3
D5 should = System 4

Each System contains unique counties. What function (s) would I need to use
to match the correct county to its respective system?

As of now I am trying to use in cell "D2":
=INDEX(H1:K5,MATCH(C2,C2:C1246,0),MATCH(H1,I2:K2,0 )) <-completely off?
obviously not working...
Any help would be greatly appreciated...