SOLVED

Converting from google sheets to excel

Occasional Contributor

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

@amuench16 

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"?

@amuench16 

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.

I can put it through onedrive, if that works

@amuench16 

Please do.

may i have your email?

@amuench16 

hans dot vogelaar at gmail dot com

I have sent the link
Thank you!!!!
best response confirmed by Sergei Baklan (MVP)
Solution

@amuench16 

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.

That worked perfectly. Thank you!!