sumproduct with vlookup

[lesbritten]

Hi all,

I have the following formula that works perfectly....
=SUM(SUMPRODUCT(AP17:AQ22*AP16:AQ16))

However, when i try to lookup a value like so....
=SUM(SUMPRODUCT(O5:Q8*VLOOKUP(O3:Q3&"-"&O4:Q4,BK6:BN35,4,FALSE)))
it doesn't work, even when I CSE the formula.

I think i am batting along the wrong lines when using vlookup and may have to use index and match, but my knowledge of these is limited.

I added an extra column to the vlookup table that combined the text from BL6:BM35 to make column BK (if you see what I mean) so the Vlookup would work.

I hope thats enough detail, any ideas/suggestions welcome!!

Many thanks,

Les

 

[theBardd]

That lookup is using multiple cells to concatenate in the lookup value. That won't work. What is in those cells and the lookup table?

 

[lesbritten]

interesting.... I've used concatenation with vlookups in the past and worked

In the vlookup table I have four columns.
Column BK is the ref that contains a concatenation of rows BL and BM with a "-" inbewteen.
Column BN contains the price

Ie.
BK would have Coriander-Bunched
BL would have Coriander
BM would have Bunched
BN would have 1.5

Rows 6-35 would have different specifications all the way down

Les

 

[theBardd]

Yeah you can with cells like this

O3&"-"&O4

but you are trying to concatenate ranges

O3:Q3&"-"&O4:Q4