Forum Discussion
Issue with converting google spreadsheet into excel
I've never worked with google sheets, but it appears to me that excel didn't recognize the existing formula, so put the existing formula in quotes and wrapped it with an iferror function to return the last calculated value.
If you have office 365 and excel's version of the filter function, then I think your formula in L2 would be:
=iferror(INDEX(filter('Transactions USD'!J:J,('Transactions USD'!C:C<>"")*(row('Transactions USD'!C:C)=max(if('Transactions USD'!C:C=B2,row('Transactions USD'!C:C),0)))),1),0)
But, you should explain further the purpose of the formula as there may be a better way to do it. For the formula in 'Stock Summary USD'!L2, It appears you are trying to return the last entry from 'Transactions USD' column J where the stock name in column C equals B2? If so, and you have office 365, then you might take a look at excel's xlookup function as it can perform a lookup from the bottom up.
https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
Otherwise, you could try:
=LOOKUP(2,1/('Transactions USD'!C:C=B2),'Transactions USD'!J:J)
Also, if you're staying with excel, you should consider converting your data tables to structured tables (select your table, then click on the Insert tab and click the table button). When you click on the structured table, a Table Tools tab will appear on the ribbon.
Then, when you write your formulas you can just select the ranges that have data and excel will use a structured table reference in the formula. You won't need to use entire column references in your formula - as your table grows, your formulas will automatically include the new rows when you use structured tables. This should help a lot with calculation speed.