SOLVED

# Converting from google sheets to excel

Occasional Contributor

# 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)))

10 Replies

# Re: Converting from google sheets to excel

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)

# Re: Converting from google sheets to excel

Unfortunately, that did not work. Does the formula require "MATCH" to be "XMATCH"?

# Re: Converting from google sheets to excel

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.

# Re: Converting from google sheets to excel

I can put it through onedrive, if that works

# Re: Converting from google sheets to excel

hans dot vogelaar at gmail dot com

# Re: Converting from google sheets to excel

Thank you!!!!
best response confirmed by Sergei Baklan (MVP)
Solution

# Re: Converting from google sheets to excel

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