Excel match multiple values

0
votes

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!

1
excelexcel-formula
If the issue occurs when dragging the formula, my guess is that worksheet2!C4:C285 is the cause, as you have absolute referencing elsewhere but not here... To be of more help a small example mock up of data with your expected output would really help us to assist you. – Glitch_Doctor
'return a certain value in E of worksheet3' doesn't jibe with worksheet2!$E$4:$E$285. In fact it is all a little confusing and made more so when the sample formula contradicts the narrative. Can you post expected results? – user4039065
updated/corrected my questing with some images and more info. sorry, made some mistakes in simplifying my data/question :) – Stoepsteen

1 Answers

0
votes

... 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))