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

[eit09]

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.

 

[IDS]

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/

 

[yakpol]

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

 

[IDS]

=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/

 

[SkipVought]

I recommend also using either Named Ranges (versions 97-2003)
[tt]
=SUMPRODUCT((LC=C3)*(Joint_Label=D3)*(Y__in))
[/tt]
or Structured Tables (versions 2007+)

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[eit09]

Thanks for the responses taking the row function out worked perfectly!

 

[onlyadrafter]

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