CODEWITHSUNDEEP
excelexcel-formula
Stoepsteen
Stoepsteen

Reputation: 21

Excel match multiple values

I'm trying to write an excel formula to return a certain value in column E of worksheet1 if the following criteria are fulfilled:

-value in column A;row x in worksheet1 matches value in column A;row y in worksheet2
-value in column K;row x in worksheet1 matches value in column C;row y in worksheet2

if a match is found, the return value should be the value in column E of worksheet 2 times a certain value in another worksheet (cell B23 in worksheet3 in this case)

images of both worksheets included below worksheet1 worksheet2 so in this case cell L122 of worksheet1 which contains the formula, should return value E6 of worksheet2 (because 05.11. and 'st' are matched)

Formula so far:

=INDEX(worksheet2!$E$4:$E$285;match(worksheet1!A1&worksheet1!K1;worksheet2!$A$4:$A$285&worksheet2!$C$4:$C$285;0))*(worksheet3!$B$23/750)

but it seems I'm doing something wrong with the match/index formula and not really a clue what causes the formula to fail :)

Thanks in advance!

Upvotes: 0

Views: 145

Answers (1)

user4039065
user4039065

Reputation:

... cell L122 of worksheet1 which contains the formula, should return value E6 of worksheet2 (because 05.11. and 'st' are matched)

Multiple column matches are simplified somewhat with AGGREGATE. Put this in worksheet1!L122

=INDEX(worksheet2!$E$4:$E$285; aggregate(15; 7;
            row($4:$285)/((worksheet2!$A$4:$A$285=worksheet1!A122)*
                          (worksheet2!$C$4:$C$285=worksheet1!K122)); 1))

Upvotes: 0

Related Questions