0

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:
SecondSheet

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.

=IF(Tabelle1!B2="Motor";VLOOKUP(A2;Tabelle1!$F:$H;2;FALSE);VLOOKUP(A2;Tabelle1!$F:$H;2;FALSE))

Improve this question

2 Answers 2

Reset to default
0

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.

Improve this answer
1
  • 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, 2019 at 5:38
0

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.

=INDEX(Tabelle1!$E:$H;MATCH(1;(Tabelle1!$G:$G=A2)*(Tabelle1!$E:$E=E$1)*(Tabelle1!$B:$B="Motor");0);4)```
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .