Converting from google sheets to excel

[amuench16]

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

[#2]

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

[#3]

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

[#4]

@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.

[#5]

I can put it through onedrive, if that works

[#6]

@amuench16 

Please do.

[#7]

may i have your email?

[#8]

@amuench16 

hans dot vogelaar at gmail dot com

[#9]

I have sent the link
Thank you!!!!

[#10 · Accepted 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.

[#11]

That worked perfectly. Thank you!!