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

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,

_{ 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