Forum Discussion

Copper Contributor

Apr 27, 2022

Solved

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

Formulas and Functions

HansVogelaar
Apr 27, 2022
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.

HansVogelaar

MVP

Apr 27, 2022

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)

amuench16

Copper Contributor

Apr 27, 2022

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

HansVogelaar
MVP
Apr 27, 2022
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.
- amuench16
  Copper Contributor
  Apr 27, 2022
  I can put it through onedrive, if that works
  - HansVogelaar
    MVP
    Apr 27, 2022
    amuench16
    
    Please do.