multiple index match

[u65646]

Hello

I'm trying to do an index match and it successfully works individually but when I try to add 4 index match one 1 line, I can't seem to get it to work.

Please find below the individual formulas that I want to incorporate on 1 line.

=IFERROR(INDEX('S&M 16th Apr'!$ET$14:$ET$213,MATCH('Cost of Sales (May 18)'!$F50,'S&M 16th Apr'!$G$14:$G$213,0)),"")

=IFERROR(INDEX('Secur Licence Rev'!$C$8:$C$107,MATCH('Cost of Sales (May 18)'!$F50,'Secur Licence Rev'!$K$8:$K$107,0)),"")

=IFERROR(INDEX('Cognos Milestone report'!$F$4:$F$2003,MATCH('Cost of Sales (May 18)'!$F50,'Cognos Milestone report'!$N$4:$N$2003,0)),"")

=IFERROR(INDEX('BPC FCST (NEW)'!$D$13:$D$5012,MATCH('Cost of Sales (May 18)'!$F50,'BPC FCST (NEW)'!$Q$13:$Q$5012,0)),"")

I'm stuck and don't want to use a vlookup to pick the answer as the format of the file can't be changed.

 

[JonXL]

Are you saying you want these in one cell?

If so, how do you want them to relate to one another?

 

[u65646]

Yes I need the formula of all the 4 index matches in one cell.

The index match is looking for a product line from each individual list (ie column ET, column C, Column F, column D)

the Index match would be based on an if statement as follows:

If E50="Secure - Licence", then IFERROR(INDEX('Secur Licence Rev'!$C$8:$C$107,MATCH('Cost of Sales (May 18)'!$F50,'Secur Licence Rev'!$K$8:$K$107,0)),"")

If E50 ="Secure - NROD", then IFERROR(INDEX('Cognos Milestone report'!$F$4:$F$2003,MATCH('Cost of Sales (May 18)'!$F50,'Cognos Milestone report'!$N$4:$N$2003,0)),"")

if E50="Secure - Professional Services", then IFERROR(INDEX('BPC FCST (NEW)'!$D$13:$D$5012,MATCH('Cost of Sales (May 18)'!$F50,'BPC FCST (NEW)'!$Q$13:$Q$5012,0)),"")

if E50 ="Secure S&M", thenIFERROR(INDEX('S&M 16th Apr'!$ET$14:$ET$213,MATCH('Cost of Sales (May 18)'!$F50,'S&M 16th Apr'!$G$14:$G$213,0)),"")