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

Complex VLOOKUP formula across multiple sheets .png

NB - please ignore E37 & E38

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

In advance, many thanks,

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.