Complex VLOOKUP formula across multiple sheets

Salèha El D.

Hi,

I need help with a complex formula, I have not managed to work out where I am going wrong 🤔.

A little background:

• I am looking at a VLOOKUP formula on a costing sheet 1 (left on picture below)
• The formula will be referencing another sheet, Sheet 2 (right on picture below)

What I am looking at is:

Look for: the delivery cost which varies on the geo-location (sheet 2 = {Costs - Costing Sheet Range 9} ; Zones 1 - 10), and add all extras if applicable.

I have:

• E33 (column 1) - zones 1-10 - straight forward zone selection / enter zone number (sheet 1)
• E34 (column 2) - If - the delivery is during the day = day rate
• E34 (column 3) - If - the delivery is at night = night rate
• E35 (column 4) - If - the delivery has add on = Add On
• E36 - SUMIFS - add all costs together

Here is the formula:

=IF(E35 = "NO",IF(E34 = "NO",(VLOOKUP(E33, {Costs - Costing Sheet Range 9},2, false)),(VLOOKUP(E33, {Costs - Costing Sheet Range 9},3, false))), SUMIFS(VLOOKUP(E33,{Costs - Costing Sheet Range 9},4,false),IF(E34 = "NO",(VLOOKUP(E33, {Costs - Costing Sheet Range 9},2,false)),(VLOOKUP(E33,, {Costs - Costing Sheet Range 9},3,false)))))

Formula located in cell = E36

NB - please ignore E37 & E38

I hope this makes sense, I am desperate for help solving this conundrum.

In advance, many thanks,

Nick Korna

I'm not 100% certain if E34 being YES is day or night, but you could amend to do the 2 VLOOKUPS independently and have them added together to get the total in E36. The below is based on E34 being "NO" meaning the day rate is being used:

=IF(E34 = "NO",VLOOKUP(E33, {Costs - Costing Sheet Range 9},2, false),VLOOKUP(E33, {Costs - Costing Sheet Range 9},3, false)+IF(E35 = "NO",VLOOKUP(E33, {Costs - Costing Sheet Range 9},4,false),0)

Hopefully this helps or at least gives you some ideas, but if you've any problems/questions then just post! 🙂

Salèha El D.

Thanks Nick,

Yes, E34 is day rate if = YES.

I tried the suggested alternative, no success - I will have a closer look tomorrow morning, I think you are right in using 2 VLOOKUPS independently.