Index Match Vlookup?

I

IntricateFool

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...
 
D

Dave Peterson

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

IntricateFool

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

IntricateFool

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

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.


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...
 
P

PapaDos

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

IntricateFool

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.
 
I

IntricateFool

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

PapaDos

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

Did you try to adjust my formula t your ranges ?
 
I

IntricateFool

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

IntricateFool

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.
 
D

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().
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...
 
B

Biff

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

IntricateFool

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.
 
D

Dave Peterson

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using index and match 5
Index match with multiple criteria(different than usual) 0
multiple vlookup; index/match 2
Index, Match within a range of values 4
How to use index and match function for determining the value? 1
Index match with multiple criteria(different than usual) 3
Index & match/IF/Vlookup formula help 5
Index & Match from Other Sheets? 2

Top