INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...just the few hours I've spent on this site I have learned a lot. I can now implement features that will be very useful to my company, and some of those I never knew existed..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > MIS/IT > Office Suites > Microsoft: Office Forum

Excel lookup Multiple Criteria Index Match Formula Issue(2)

Read More Threads Like This One

Randy11 (TechnicalUser)

20 Jul 12 13:03

Am performing a lookup in Excel 2007 with two pieces of criteria. If both lookup criteria (Home & Code) are met, info in named range(Location) to be returned. Problem is it is only returning the result if it is the 1st row containing the Home criteria that criteria matches. Assistance appreciated.

If I lookup 11 & Drone 320 is answer
If I lookup 11 & Colter N/A# is answer

Formula used is:
=INDEX(Location,MATCH($A2,IF(Code=C$1,Home),0))
Note: this lookup is on another worksheet. A2 has the Home number C1 has the Code that form the criteria for the lookup.

Sample Data (Each Column is a Named Range. Column A is "Home", B is "Location", C is Code.

Home Location Code
11 320 Drone
11 600 Colter
11 780 Knookums
11 790 NonDruids
13 830 Drone
13 1150 Colter
13 2250 Knookums
13 2610 NonDruids
15 9280 Drone
15 10320 Colter
15 10781 Knookums
15 10790 NonDruids

(2) SkipVought (Programmer)

20 Jul 12 14:04

hi,

Using Named Ranges of your table...

=INDEX(Location,MATCH(G3,Home,0)-1+MATCH(H3,OFFSET(Home,MATCH(G3,Home,0)-1,2,COUNTIF(Home,G3),1),0),1)

where G3 contains the Home criteria and H3 the Code criteria.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}