0

In a spreadsheet from work, prices per product per period are tracked. I want to create a table in which I state the price per product on 1 January and 1 July for each year (see image for a simplified example). For this I want to look up: 'if product name matches and date fall in between date range, take over accompanying price'. For this I intend to use INDEX+MATCH. However, I keep getting errors after trying uncountable times.

For cell H3 (see image), I've tried using the following formula:

=iferror(index($D$2:$D$21,match(1;($G3=$A$2:$A$21)*(H$2>=$B$2:$B$21)*(H$2<=$C$2:$C$21;0)))

...and many variations of this formula (including small adaptation like changing ; into ,) in Excel 2016. I am aware that I should type Ctrl+Shift+Enter since it's an array formula.

Could you please help my getting the formula right so it works in Excel?

Now I have tried the formula =iferror(index($D$2:$D$21;match(1;($G3=$A$2:$A$21)*(H$2>=$B$2:$B$21)*(H$2<=$C$2:$C$21);0)),"") and still get an error. See screenshots with the code and with the error I get.

Any suggestions? The dates are in mm/dd/jjjj format now.

Anne-sophie
  • 1
  • 1
  • If your dates do not overlap per product, use SUMIFS. See:https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another – Scott Craner Nov 20 '20 at 16:54
  • by the way you missed a `,` to `;` and your `)` are off: `=iferror(index($D$2:$D$21;match(1;($G3=$A$2:$A$21)*(H$2>=$B$2:$B$21)*(H$2<=$C$2:$C$21);0)),"")` – Scott Craner Nov 20 '20 at 16:56
  • Thank you for the suggestions! I've now tried =iferror(index($D$2:$D$21;match(1;($G3=$A$2:$A$21)*(H$2>=$B$2:$B$21)*(H$2<=$C$2:$C$21);0)),"") and still get an error (see the edited description above). Happy with any input that may help! – Anne-sophie Dec 03 '20 at 09:35
  • I missed a `,` to `;` at the end. you need all the `,` to be `;` or all to be `,` do not mix and match. – Scott Craner Dec 03 '20 at 14:03

0 Answers0