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.
=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
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
=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
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
=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,
Just traded in my OLD subtlety...
for a NUance!
RE: How do you use the Index formula using two criteria to return a value from a table in excel
RE: How do you use the Index formula using two criteria to return a value from a table in excel
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