0

I have managed to create a very working formula in Google Spreadsheets, but when downloading it as Excel, the formula doesn't work. Also typing in the formula in Excel (2016) does not work (when using an identical formula as used in Spreadsheets).

I have a spreadsheet with products and data on each product's price per period (see start_date and end_date in left table in example spreadsheet). In Google Spreadsheets, I managed to create an INFERROR+INDEX+MATCH formula which enables an overview table which states the prices per product on one row for each specified date (see right table in example). I have tried to copy/paste the formula in Excel, as well as typing it over and changing some ';' into ',' and other minor changes. however, I still didn't manage to make it work in Excel.

When looking at my example, I've used the following formula for cell H3 in Google Spreadsheets:

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

Could you tell me what the problem may be in using this formula in Excel 2016 and possibly show me how to make it work in Excel?

Example from Google Spreadsheets (screenshot)

Follow-up steps and question:

Now I have adapted & entered the formula in Excel as follows:

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

..and then pressed ctrl + shift + enter.

Excel gives me the error:

'You've entered too few arguments for this function'

Do you see any other issues with my formula that could cause this particular error in Excel 2016?

Happy to hear about your insights!

Anne-sophie
  • 1
  • 1
  • For me references like $d$2:$d are meaningless. Excel can't understand that (nor can I). Try using d:d ? – iDevlop Nov 10 '20 at 12:57
  • @Patrick yes but Google sheets can. I guess that's the point of the question – chris neilsen Nov 10 '20 at 14:06
  • 1. As stated above open ended ranges like `$D$2:$D` do not work in Excel. You will need to stated the ending row. 2. This is an array formula and in Excel 2016 it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. – Scott Craner Nov 10 '20 at 14:45
  • Please see: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another for other options on how to do multi-column matches. – Scott Craner Nov 10 '20 at 14:47
  • Thank you for your input & I am happy to hear if you have advice for the next issue I encountered (see new description above). – Anne-sophie Nov 13 '20 at 09:55

1 Answers1

0

This is an array-formula. When entering the formula in excel you need to enter it using ctrl + shift + enter.

Krzysztof Madej
  • 32,704
  • 10
  • 78
  • 107
user14618570
  • 89
  • 3
  • Thank you for your input! Now I have entered the formula in Excel as follows: =iferror(index($D$2:$D$21;match(1;($G3=$A$2:$A$21)*(H$2>=$B$2:$B$21)*(H$2<=$C$2:$C);0))) and then pressed ctrl + shift + enter. Excel gives my the error 'You've enteref too few arguments for this function. Do you perhaps see any other issues with my formula that could cause this particular error in Excel 2016? – Anne-sophie Nov 13 '20 at 09:46
  • Maybe your excel expects comma `,` separated arguments instead of `;`? – user14618570 Nov 13 '20 at 15:39
  • Also in the last mentioned range there's the same open range: `$C$2:$C` I guess you should refer to it as `$C$2:$C$21`. – user14618570 Nov 13 '20 at 15:40
  • Thank you for your advice! =iferror(index($D$2:$D$21;match(1;($G3=$A$2:$A$21)*(H$2>=$B$2:$B$21)*(H$2<=$C$2:$C21);0))) still doesn't work. Does anyone have tips for me? I tried it with , and ; – Anne-sophie Nov 20 '20 at 16:18
  • You are using Iferror, but you didn't specify what to do if the result is an error.type that before the final parenthesis. For instance `;"")` to give a blank result of error. – user14618570 Nov 20 '20 at 16:33