Thread: Conditional Array & VLOOKUP: Example file attached

Excel Dummy Data Attached - refer red cell H24. Use formula precedent to trace from red cells H24.

Appreciate assistance. Currently, I am generating a hash/value sign.

Formula Outline
Cost=Function(conditionals in array, fibre distance in array, cable quantity in array, cable type in array, price of cable based on cable type)

Note - the price schedule is part of the VLOOKIP but is not the same size as the other arrays, but it cannot be. But obviously this creates a problem.

Assistance appreciated.
Thanks

You can't really use VLOOKUP within SUMPRODUCT but first things first the VLOOKUP itself doesn't make sense:

VLOOKUP($BZ$7:$BZ$15,$C$39:$C$49,K38,FALSE)

ie the table_array contains only one column - I think C49 should be Q49, correct ?

Given the restriction on VLOOKUP, current config. (numerical nature of the condition) then based on your sample file you could perhaps use:

H31:
=SUMPRODUCT(($D$7:$D$15="CO")*($H$7:$H$15="C")*($I$7:$I$15="Y")*$AS$7:$AS$15*$BJ$7:$BJ$15*SUMIF($C$39:$C$49,$BZ$7:$BZ$15,H$39:H$49)*H22)