Apr 27 2022 08:51 AM
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)))
Apr 27 2022 09:49 AM - edited Apr 27 2022 09:50 AM
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)
Apr 27 2022 09:55 AM
Apr 27 2022 09:57 AM
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.
Apr 27 2022 11:42 AM
Apr 27 2022 12:26 PM
hans dot vogelaar at gmail dot com
Apr 27 2022 12:55 PM
SolutionThe 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.
Apr 27 2022 12:55 PM
SolutionThe 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.