This is the sheet I am working on. The trouble starts on the tab titled "Sectors and subsectors", on row 56 under the column named "checksum".

What I am attempting to do is as follows. The checksum row is supposed to return the human population in the sector whose name is listed in the column "Sector name". This is to make sure that all of the corresponding subsectors (listed in the column titled "Subsector name") for that sector have a population that sums up to the population of the sector they are in, with the task of subtraction done in the column titled "diminution".

I placed a lookup function in order to find the corresponding values for human population and sector names on the sheet named "Sector list", which is an importrange of the tab "Sectors" on this spreadsheet.

All the subsectors which have the sector name Five were intended to have the lookup formula under the column named "checksum" do the following:

looks up the sector on "Sector list" with an identical value for its name as the Sector name column on "Sectors and subsectors"

looks up the value for human population on "Sector list" in the same row as the sector, and return it

For the preceding ten rows in the checksum column the basic Lookup function worked. However, for rows 56-65, it returned the #N/A error "Did not find value "Five" in lookup evaluation".

...When it's clearly there.

I attempted to use VLOOKUP next. That has the #REF! error "VLOOKUP evaluates to an out of bounds range".

Any help in sorting this out is greatly appreciated.

1 Answer 1


The formula 'Sectors and subsectors'!F56 is

=vlookup(B56,'Sector list'!A$2:A$40,2,FALSE)

The error ocurrs b/c the third parameter is 2 but the second parameter only has one column.

The formula 'Sectors and subsectors'!F57 is

=lookup(B57,'Sector list'!A$2:A$40,'Sector list'!B:B)

The value in B57 is Five. The error ocurrs b/c the values in 'Sector list'!A$2:A$40 aren't sorted ( Four is before Five)

  • 1
    Sorry for replying late. I updated the formula in F56 to have both the columns containing the sector names and the population listed, to =vlookup(B56,'Sector list'!A$2:B$40,2,FALSE). That seems to have worked. I do know LOOKUP isn't the best for doing sorts. Thanks for letting me know some more about how these formulas work, it's my first time using the lookups.
    – Sam Munroe
    Commented Nov 11, 2020 at 18:05
  • Not late at all. Commented Nov 11, 2020 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.