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