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
thmhAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello thmh,

try this:

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

cheers, teylyn
0
 
DaveCommented:
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
 
MSmaxImplementation ConsultantCommented:
try:

=SUMPRODUCT(--(A1:A6=K1),--(B1:B6=L1),(C1:C6))
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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
 
thmhAuthor 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.

All Courses

From novice to tech pro — start learning today.