Irfanilham
New Member
 Joined
 Oct 2, 2020
 Messages
 4
 Office Version

 2016
 Platform

 Windows
I have question is it possible to combine a normal formula and array formula because as of my knowledge one only requires enter and another ctrl shift and enter. How do I combine them both if it is possible?
=IF(IFNA(VLOOKUP(M2,CHOOSE({1,2},IF('[Tier Price List.xlsx (Active)]Sheet4'!$A$2:$A$5=AX2,'[Tier Price List.xlsx (Active)]Sheet4'!$E$2:$E$5,""),IF('[Tier Price List.xlsx (Active)]Sheet4'!$A$2:$A$5=AX2,'[Tier Price List.xlsx (Active)]Sheet4'!$F$2:$F$5,"")),2,TRUE),"")<>"",
IFNA(VLOOKUP(M2,CHOOSE({1,2},IF('[Tier Price List.xlsx (Active)]Sheet4'!$A$2:$A$5=AX2,'[Tier Price List.xlsx (Active)]Sheet4'!$E$2:$E$5,""),IF('[Tier Price List.xlsx (Active)]Sheet4'!$A$2:$A$5=AX2,'[Tier Price List.xlsx (Active)]Sheet4'!$F$2:$F$5,"")),2,TRUE),""),
IF(IFNA(VLOOKUP(K:K,'[Price List 2020.xlsx (Active)]baseline format'!$D:$K,6,FALSE),"")<>"",IFNA(VLOOKUP(K:K,'[Price List 2020.xlsx (Active)]baseline format'!$D:$K,6,FALSE),""),IFNA(VLOOKUP(I2&K2,'[Price List 2020.xlsx (Active)]baseline format'!$A:$J,9,FALSE),"")))
p.s. (The formula in bold are the array formula)
=IF(IFNA(VLOOKUP(M2,CHOOSE({1,2},IF('[Tier Price List.xlsx (Active)]Sheet4'!$A$2:$A$5=AX2,'[Tier Price List.xlsx (Active)]Sheet4'!$E$2:$E$5,""),IF('[Tier Price List.xlsx (Active)]Sheet4'!$A$2:$A$5=AX2,'[Tier Price List.xlsx (Active)]Sheet4'!$F$2:$F$5,"")),2,TRUE),"")<>"",
IFNA(VLOOKUP(M2,CHOOSE({1,2},IF('[Tier Price List.xlsx (Active)]Sheet4'!$A$2:$A$5=AX2,'[Tier Price List.xlsx (Active)]Sheet4'!$E$2:$E$5,""),IF('[Tier Price List.xlsx (Active)]Sheet4'!$A$2:$A$5=AX2,'[Tier Price List.xlsx (Active)]Sheet4'!$F$2:$F$5,"")),2,TRUE),""),
IF(IFNA(VLOOKUP(K:K,'[Price List 2020.xlsx (Active)]baseline format'!$D:$K,6,FALSE),"")<>"",IFNA(VLOOKUP(K:K,'[Price List 2020.xlsx (Active)]baseline format'!$D:$K,6,FALSE),""),IFNA(VLOOKUP(I2&K2,'[Price List 2020.xlsx (Active)]baseline format'!$A:$J,9,FALSE),"")))
p.s. (The formula in bold are the array formula)