0

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

Eddie Maiale
  • 23
  • 1
  • 5
  • 2
    Vlookup uses the first column in the supplied range/table to do the lookup. You are trying to lookup `Chromebook(G3)` which is clearly in the second column. Change from `inventory` to `C:F` so you look up the correct column or rearrange your table to have the description be the first field. – JNevill May 31 '18 at 18:48
  • Thank you @JNevill, that clears up why VLOOKUP isn't working. I didn't know that about Vlookup. I haven't had the time to work out any changes yet, but I will as soon as I get home! I am really intrigued by the Index/Match function instead of Vlookup as it seems that it provides more functionality. I must have just glanced over the first column thing. Thanks! – Eddie Maiale Jun 01 '18 at 18:57

3 Answers3

2

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.

enter image description here

0

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),"")
vmsmithson
  • 11
  • 2
  • 1
    You're just not going to be able to use vlookup to match a value in the second column of the structured table. –  May 31 '18 at 23:18
  • 1
    Yes, you are right! I overlooked my formula. Try, =iferror(vlookup(k7,inventory[[#All],[Description]:[QTY]],3,false)"") – vmsmithson Jun 01 '18 at 10:57
0

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)

urdearboy
  • 14,439
  • 5
  • 28
  • 58