Dropdown text to number for calculations, VLOOKUP error

Question

Level 1

8 points

Dropdown text to number for calculations, VLOOKUP error

The setup is as follows:

Column A contains the name of the item, B contains a total amount of the item, C contains the types and numbers of containers that item is stored in, D is the column in which I need the dropdown as there are multiple different types of containers with different properties, Column E is where I would like the associated value with the dropdown to enter such that when I select "Hotel Pan" it returns 3 or "6 pan" it returns 0.25 and so on. And Column F is where I need the calculation to run to return a final result. e.g. B2-(C2xD2).

The Issue:

I'm have a table where one column is a dropdown menu where the options are text or a "string" and I need it to return numerals such that I can use the information for calculations. I have tried creating a new table with the reference data and writing a VLOOKUP function but I get the error "The formula contains an invalid reference." but it doesn't show what the invalid reference is.

It appears that there is no actual way to have a cell contain a string but return numerals when referenced in calculations so I have the cell with the dropdown returning numbers in the column next to it and I'm using that column to create the calculation function to return my end result but it's not working.

Posted on May 21, 2026 2:39 PM

Reply

Answer 1

Top-ranking reply

Camelot

Level 9

60,027 points

May 22, 2026 11:15 AM in response to Shagee08

There's nothing wrong with using VLOOKUP(), but since you don't show the formula you're using it's impossible to say what's wrong for sure, but there are common mistakes that can do this.

It is true that XLOOKUP() adds additional functionality, but the only real advantage in this case is that it handles missing values, but if you're choosing from a preset list you're not likely to have a missing value, so that benefit is moot.

The other difference that most people miss is that with VLOOKUP, you provide three critical pieces of information:

The first is the value to lookup.

Second is a range to search - this has to include BOTH the column of lookup values AND the column containing the results you want to return.

The third parameter is the column index of the results.

That last one is what most people (myself included) usually miss. Unlike XLOOKUP where you provide a column for the search terms, and a separate column for the results, VLOOKIP takes BOTH columns in one, and an indicator as to which column the results should come from.

For example, assuming your table of containers looks something like:

The function you want in in your main table would be:

=VLOOKUP(C2,Table 2::A:B,2,FALSE)

This tells Numbers to take the value in C2 and look for it in the range Table 2::A:B (i.e. BOTH columns A and B from Table 2). For an exact match (denoted by the 'FALSE' at the end, return the value in the SECOND column (i.e. column B).

Now you'll get a numeric value from column B corresponding to the string in column A.

Many people erroneously think that you need to provide a specific column/range for the third parameter (the results column) which is not the case for VLOOKUP(), and will result in the error message you're seeing.

As said above, XLOOKUP() is more flexible, but also more complex. Choose whichever model you prefer.

Reply

Answer 2

Badunit

Level 7

31,162 points

May 22, 2026 8:08 AM in response to Shagee08

If you have a dropdown in column D and you have a table elsewhere where you can use XLOOKUP (VLOOKUP is ancient) to look up a number based on the value in column D, the formula in E will be of the form

LOOKUP(D2,Table 2::A,Table 2::B,, "Not Found",0))

And your other formula will be

=B2-(C2*E2)

If the result for the lookup is also dependent on the type/name of item (from column B), not just the dropdown in D, you can use SUMIFS in place of XLOOKUP to do a multi-variable lookup. It can also be done with the array functions.

Reply