# index match 2 values in row excel

hy , how to match 2 values in same row
example:

A     B      C  ...    K     L      M
1 aaa  bbb    1        ccc bbb    6   in M1 formula needs to index column C and match K1 and L1
2 aaa  ccc    2                               with column A and B
3 bbb  ccc    3
4 ccc  aaa    4
5 bbb  aaa    5
6 ccc  bbb    6
###### Who is Participating?

Microsoft MVP ExcelCommented:
Hello thmh,

try this:

=INDEX(C1:C100,MATCH(K1&L1,INDEX(K1:K100&L1:L100,0),0))

cheers, teylyn
0

Commented:
In K1
=INDEX(\$A\$1:\$A\$6,MATCH(M1,\$C\$1:\$C\$6,0))

L1
=INDEX(\$B\$1:\$B\$6,MATCH(M1,\$C\$1:\$C\$6,0))

hth

Dave
0

Implementation ConsultantCommented:
try:

=SUMPRODUCT(--(A1:A6=K1),--(B1:B6=L1),(C1:C6))
0

Microsoft MVP ExcelCommented:
The Sumproduct will work, but only if the values in column C are numeric. The Index/Match will return any data type.

If there is more than one match, the Sumproduct will add all the numeric values, or zero for text. The Index/Match will return the value of the first match.

cheers, teylyn
0

Author Commented:
tnx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.