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)
mohammed3786 Don't know anything about Google Sheets, but when I open your file, I find this in L2 on the Summary sheet:
=IFERROR(@__xludf.DUMMYFUNCTION("iferror(INDEX(arrayformula(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)"),6.5)It seems you have used a user defined function in GS. Obviously, it is not recognised by Excel. and "array formula" isn't something Excel would recognise either. In addition to that, the expressions in the "filter" function aren't compatible with those for Excel either. So, what it is that you want to calculate in column L?
