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!
$D$2:$Ddo 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