Issue with VLOOKUP when using a named range table

Question

I am having an issue with getting excel to recognize the table in my VLOOKUP formula. This is the formula that I am trying to use to show the current quantity based on a selection from a dropdown list.

=IFERROR(VLOOKUP(K7,inventory,3,0),"")

Where K7 is the dropdown list I created referencing my table, "inventory". I have named my table "inventory" and I have tried converting my table back to ranges and re-tabling the data with the same error. When I take out the IFERROR("") part of the formula I get an #N/A error. I also have conditional formatting on my table that highlights/bolds rows that meet a certain condition (a column value is greater than another column value). The table is in the same Worksheet as the dropdown list. I do not have any VBA code or projects in my workbook currently.

Ultimately what I am trying to do with this is to grab the current value in the table using the dropdown list and alter the value by adding or subtracting by a number specified by the user.

Context Image

Answer 1

If your table is called "inventory", and you want a value in column "QTY" based on a value in K7, try the below formula.

=iferror(vlookup(k7,inventory[[#All],[Description]:[QTY]],3,false),"")

Answer 2

You need to modify the equation since your table array needs to start where your lookup value starts (IF you are using a VLOOKUP. There is another equally valid solution above that uses the INDEX/MATCH functions).
You have more control with those functions and can reference your table, but the downside is that those equations are not intuitive to less excel oriented users. You are experiencing a downside of the VLOOKUP, but it is easier to read and understand to the average excel user. (moral is both have + / -)

=VLOOKUP(K7, C:E, 3, 0)

This above equation will find your lookup value (K7) and return the corresponding value in Column E. If you need the corresponding value in column F, you would adjust the equation as follows:

=VLOOKUP(K7, C:F, 4, 0)

Answer 3

If you want to work with your structured table references, abandon VLOOKUP and use an INDEX/MATCH function pair instead. This will allow you more control over which column is used for the lookup and which column is used for retrieval. Wrapping the formula in IFERROR shouldn't be necessary since a properly configured drop-down list should always return a value.

=index(inventory[qty], match(K6, inventory[description], 0))

Quite simply, lookup a match in the description column and return the value from the corresponding qty column.