vlookup returning #NA as value not found in all ranges

[RolandK]

Hello all,

I have a spreadsheet with several tabs for different sites for the company.
Not all sites deliver the same service items, but most do overlap. Service items are provided at several levels, again, not all the same levels are provided by each site. The maximum is 8 levels for any of the service items. Total number of service items is 90.

All the site sheets have identical lay-out to the collation I am trying to set up, so if I want to add the values from Columns D, it will be in Column D on all the site spreadsheets.
I want to add all the returns together, but my vlookup returns a #NA if it cannot find the value on any of the site sheets.

All ranges have the site name attached and are sorted alphabetically.

Can anybody help me to calculate the total for each of the service items and each of the service levels?

Thanks in advance,

RolandK

 

[SteveO59L]

try posting a sample of your data and what you would like to do with it

 

[MarcelBeug]

Possibly you can use the "Consolidate" functionality on the DATA tab.

 

[RolandK]

Possibly you can use the "Consolidate" functionality on the DATA tab.

A very basic display of my lay out would be as follows:

A B C D E F G H
1
2
3
4

Same layout for all sheets. A1 will have the value for service 1, at level A, B2 will have service B at level 2, C4 will have service 4, upto level C, etc.. The values will be 0 if not offered.

I want all service levels for all servise at all 7 locations to be added up, inserting a zero value for any service or service level that is not available in a particular location.

E.g Location 'Here' has all services, but only upto level E, but location 'There' only has service 1,3 and 4, but at all levels.

The problem arises when a location does not offer the service: it will give me the #NA error, which then means I cannot add the values together.

Hope that explains it better..

RolandK

 

[MarcelBeug]

Hope that explains it better..

Well, not really. The layout is too basic to understand and you are mixing up letters and numbers for services and service levels.

Nevertheless: you might use the IFERROR function to return 0 if your function ends in error, like =IFERROR(your function,0)