I have an Excel Workbook with two Sheets.

In one sheet I have these values:
First sheet

and in the second sheet there are these values:

What I want is that in the Second sheet the cell E to have values of Prognose from sheet 1 where "Motor" of module_team and "OM470" of series gives values based on Datum_produktion in the sheet 2.

The formula that I have tried till now is this but it doesn't seem to give me the values that I need.



VLOOKUP was designed for a single column of matching data. You need Tabelle1!B:B to match motor, Tabelle1!D:D to match E1 and Tabelle1!F:F to match A2. Put this in E2.

=INDEX(Tabelle1!$G:$G; AGGREGATE(15; 7; ROW($1:$999)/((Tabelle1!$D$1:$D$999=E$1)*(Tabelle1!$F$1:$F$999=$A2)*(Tabelle1!$B$1:$B$999="motor")); 1))

Adjust all the 999's to reflect the actual number of rows you have in the Tabelle1 worksheet.

If you get that working, you should be able to copy the formula to G2, I2, K2, etc. then fill down for additional dates.

  • Thank you for the Reply, I adjusted the 999's for ther actual rows in the Tabelle1 but it gives me the #NUM! in every line. In the Option Excel in Calculation part i checked the Enable Iterative calculation and still it wont Change the #NUM! value. – Eidrizi Sep 2 '19 at 5:38

So for the ones who have similar Trouble in the future, i used this Code and it helped me. Use Ctrl + Shift + Enter and the Code will work.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.