Forum Discussion
Converting from google sheets to excel
Hello! My company recently did a complete changeover from google to microsoft. There is a very useful document that wasn't translating the formulas correctly. There is no "error" response but it will not calculate. Can anyone help, this is an example:
=IF(ISERROR(INDEX(INDIRECT(""&C$2&"!$G4:$AA"),MATCH($B3,INDIRECT(""&C$2&"!$F4:$F"),0),MATCH($C$1,INDIRECT(""&C$2&"!$G$3:$AA$3"),0))),"",INDEX(INDIRECT(""&C$2&"!$G4:$AA"),MATCH($B3,INDIRECT(""&C$2&"!$F4:$F"),0),MATCH($C$1,INDIRECT(""&C$2&"!$G$3:$AA$3"),0)))
The formula in C3 should be
=IFERROR(INDEX(INDIRECT("'"&C$2&"'!$G4:$AA$1000"),MATCH($B3,INDIRECT("'"&C$2&"'!$F4:$F$1000"),0),MATCH($C$1,INDIRECT("'"&C$2&"'!$G$3:$AA$3"),0)),"")
Fill down and to the right. And the formula in C24 should be
=IFERROR(INDEX(INDIRECT("'"&C$2&"'!$AE4:$AY$1000"),MATCH($B24,INDIRECT("'"&C$2&"'!$AD4:$AD$1000"),0),MATCH($C$1,INDIRECT("'"&C$2&"'!$AE$3:$AY$3"),0)),"")
Also fill down and to the right.
10 Replies
The last row number of two ranges is missing. Try
=IFERROR(INDEX(INDIRECT(""&C$2&"!$G4:$AA$1000"),MATCH($B3,INDIRECT(""&C$2&"!$F4:$F$1000"),0),MATCH($C$1,INDIRECT(""&C$2&"!$G$3:$AA$3"),0)),"")
If the data on the sheet you're referring to has more than 1000 rows, adjust the number 1000 in the formula accordingly (both times)
- Unfortunately, that did not work. Does the formula require "MATCH" to be "XMATCH"?
That formula DOES work - I have tested it. But it returns a blank if there is no match.
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.
