Complex VLOOKUP formula across multiple sheets
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 geolocation (sheet 2 = {Costs  Costing Sheet Range 9} ; Zones 1  10), and add all extras if applicable.
I have:
 E33 (column 1)  zones 110  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,
Salèha El D.
Talks about #Smartsheet #productivity, #eventindustry, and #informationtechnology
➖Continuous improvement – for me, an ongoing desire to enhance processes, and increase productivity ➖
Answers

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! 🙂

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.
Salèha El D.
Talks about #Smartsheet #productivity, #eventindustry, and #informationtechnology
➖Continuous improvement – for me, an ongoing desire to enhance processes, and increase productivity ➖
Help Article Resources
Categories
Check out the Formula Handbook template!