How can i pass specific values from one sheet to another sheet based on specific criterias

1
votes

I have an Excel Workbook with two Sheets. In one sheet I have these values:

Sheet1

and in the second sheet i have this values:

Sheet2

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

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

I also tried this formula but it gives me the #NUM! value.

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

I adjusted the Settings in the Calculation part by checking the Enable Iterative but still it doesnt work.

An exmaple how should look in the Motor under OM470 sheet.

Motor sheet

Your help is much appreciated!

2
excelexcel-formula
Yes they are consistent.. – Eidrizi
Please confirm what is the data format in series, Datum_Produktion and Prognose? Also why is there 0 in front of the numbers in Prognose? – Terry W
Series and Prognose has the General Format and the Datum_produktion* has Date Format with sample (01.06.2017). the first day of the month for each series Prognose is 0. – Eidrizi

2 Answers

0
votes

This line:

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

checks if B2 = 'Motor' in Sheet 1
if so, it looks up the date in Col A (Sheet 2) and returns the value from Sheet 1 in the row matching the Col A value
if not, it does exactly the same thing.

If you want 0 or null (I'll assume 0) in the cells where the value in Sheet1!B2 is not 'Motor', change your lookup formula to:

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

Change the '0' to "" if you want an empty cell.

Based on your comment (you want the figure when the value in Col B = 'Motor' and the value in Col D = 'OM473') change the formula to:

=IF(AND(Tabelle1!B2="Motor", Tabelle1!D2="OM473"); VLOOKUP(A2; Tabelle1!$F:$H; 2; FALSE); 0)
0
votes

You may use SUMPRODUCT to solve your issue:

=SUMPRODUCT((Nr_module_team=100)*(series="OM470")*(Datum_Produktion=I2)*Prognose)

I have used a few named ranges in my formula:

  • Nr_module_team: Column A on your Sheet1;
  • series: Column D on your Sheet1;
  • Datum_Produktion: Column F on your Sheet1;
  • Prognose: Column G on your Sheet1,

You can replace these named ranges with actual cell references.

SUMPRODUCT

Cheers :)