INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • 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!

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

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How do you use the Index formula using two criteria to return a value from a table in excel
2

How do you use the Index formula using two criteria to return a value from a table in excel

How do you use the Index formula using two criteria to return a value from a table in excel

(OP)
I am trying to take two criteria values to pick a value from a table. My table values range from I3:M326. First criteria is in cells C3 to match value from table I3:I326. Second criteria is in cells D3 to match value from table J3:J326. The results I want to obtain need to go in cell F3 from data L3:L326. The formula I am using in cell F3 is
=INDEX($L$3:$L$326,SUMPRODUCT(($I$3:$I$326=C3)*($J$3:$J$326=D3)*ROW($L$3:$L$326)),0). Looking for some help with this type of formula or some suggestions. I have attached the excel file for clarity.

RE: How do you use the Index formula using two criteria to return a value from a table in excel

You are getting the result 2 rows below the one you want, except for the last one where this would be past the end of the table.

The problem is that ROW($L$3:$L$326) returns the absolute row number, rather than the row relative to the top of the table. You can get the correct result with:

=INDEX($L$3:$L$326,SUMPRODUCT(($I$3:$I$326=C3)*($J$3:$J$326=D3)*(ROW($L$3:$L$326)-2))).

Or if you prefer something without a hard coded row number:
=INDEX($L$3:$L$326,SUMPRODUCT(($I$3:$I$326=C3)*($J$3:$J$326=D3)*(ROW($L$3:$L$326)-ROW($L$2))))

Note that the final ,0 you had is not necessary because you are using index on a single column.

I can't think of a simpler single cell formula right now, but if I was doing it I'd probably use two "helper columns" using the Match function to return the row index number.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: How do you use the Index formula using two criteria to return a value from a table in excel

I modify your formula in cell F3.
=SUMPRODUCT(($I$3:$I$326=C3)*($J$3:$J$326=D3)*($L$3:$L$326))

Cool use of SUMPRODUCT function, I never remember it exists.

Yakpol

RE: How do you use the Index formula using two criteria to return a value from a table in excel

Quote:

=SUMPRODUCT(($I$3:$I$326=C3)*($J$3:$J$326=D3)*($L$3:$L$326))

OK, that's simpler :)

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: How do you use the Index formula using two criteria to return a value from a table in excel

I recommend also using either Named Ranges (versions 97-2003)

=SUMPRODUCT((LC=C3)*(Joint_Label=D3)*(Y__in))

or Structured Tables (versions 2007+)

=SUMPRODUCT((Table1[LC]=C3)*(Table1[Joint Label]=D3)*(Table1[Y '[in']]))
[/tt]
Makes for better documented expressions.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How do you use the Index formula using two criteria to return a value from a table in excel

(OP)
Thanks for the responses taking the row function out worked perfectly!

RE: How do you use the Index formula using two criteria to return a value from a table in excel

Hello,

how about (in F2)

=INDEX($L$3:$L$326,MATCH(C3&D3,$I$3:$I$326&$J$3:$J$326,0))

and enter with CTRL + SHIFT + ENTER

then copy down as far as required.

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close