Index Match Vlookup?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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))
 
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!
 
I could not follow what you had.

=index(othersheet!$c$1:$c$100,match(1,(a2=othersheet!$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?
 
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.


I could not follow what you had.

=index(othersheet!$c$1:$c$100,match(1,(a2=othersheet!$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?
Click to expand...
 
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.
 
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.
 
Im sorry, if it takes you through a roundabout way to download the file, but
it is there....
 
Sorry, I never open files coming from that kind of source.

Did you try to adjust my formula t your ranges ?
 
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?
 
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.
 
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().
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.
Click to expand...
 
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
 
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.
 
I'm not sure a workbook would help. I still don't understand the problem.
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.
Click to expand...
 
Back
Top