CODEWITHSUNDEEP
excelexcel-formula
kalame04
kalame04

Reputation: 75

Nested IFs with VLOOKUPS, 3rd condition not executing

I have a formula that calculates a value using 3 variables so I had to make a Frankenstein of a formula to make it work before I even went the VBA route.

=IF('TilePIx BOM_Launch'!E16=4, VLOOKUP('TilePIx BOM_Launch'!D16,Shipping!Q8:S14, IF('TilePIx BOM_Launch'!C16="Ground", 3, 2), FALSE), IF('TilePIx BOM_Launch'!E16=5, VLOOKUP('TilePIx BOM_Launch'!D16,Shipping!Q15:S21,IF('TilePIx BOM_Launch'!C16="Ground", 3, 2), FALSE), IF('TilePIx BOM_Launch'!E16=6, VLOOKUP('TilePIx BOM_Launch'!E16, Shipping!Q22:S28, IF('TilePIx BOM_Launch'!C16="Ground", 3, 2),FALSE), "n/a")))

Basically, excel is not calculating the result when BOM_Launch!E16=6.

Dont know why, but 4 & 5 are working flawlessly.

Upvotes: 0

Views: 74

Answers (1)

I Like Excel Very Much
I Like Excel Very Much

Reputation: 26

Change:

IF('TilePIx BOM_Launch'!E16=6, VLOOKUP('TilePIx BOM_Launch'!E16, Shipping!Q22:S28, IF('TilePIx BOM_Launch'!C16="Ground", 3, 2),FALSE), "n/a")))

to

IF('TilePIx BOM_Launch'!E16=6, VLOOKUP('TilePIx BOM_Launch'!D16, Shipping!Q22:S28, IF('TilePIx BOM_Launch'!C16="Ground", 3, 2),FALSE), "n/a")))

It's referencing a different cell than the other VLOOKUP()'s.

Upvotes: 1

Related Questions