Issue with VLOOKUP when using a named range table
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.
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.
- Constrain a Specman list so it doesn't have identical values in consecutive elements
- How to type cast a list of uint to a list of vr_ahb_data in Specman?
- Static fields/methods in e
- What is the difference between deep_copy and gen keeping in Specman?
- Specman UVM: What is the difference between write_reg { .field == 2;}; and write_reg_fields?
- Does Specman support optional parameters to a method?
- Specman e: When colon equal sign ":=" should be used?
- Specman e: Is there a way to know how many values there is in an enumerated type?
- Specman e error: No match for file when using "for each line in file"
- How to run e file one by one? Not in parallel test
- Specman/e constraint (for each in) iteration
- Specman e: How driver's items queue can be locked from a sequence?
- Specman e: How to print variable's address?
- Specman e: "keep type .. is a" fails to refine the type of a field
- Specman soft select on variable, decimal vs. hexadecimal values
- Specman e: How to print a pointer to a struct?
- Specman e: Is there a way to print unit instance name?
- Specman e: simultaneous events error
- Specman e coverage: ignored values appear in the coverage statistics
- Specman e: How a sequence should be started when gen_and_start_main constrained to FALSE?
- Specman/e list of lists (multidimensional array)
- Does Specman e have struct constructor?
- Specman e: How the predefined sequence.item should be used?
- Specman e: define-as-computed macro error
- Specman e UVM: Why to inherit from uvm_* units?
- Specman e: A sequence drives its BFM also its MAIN was not defined in a test
- e HVL (IEEE 1647): expect expression fails unexpectedly
- Specman e subtyping: How to refer to FALSE value of conditional field in when/extend subtyping?
- e HVL (IEEE 1647): How to set 'X' value?
- Difference between declaring an event that is sensitive to a simple_port value and event_port