Select Drop Down Item and use VLOOKUP to Return Multiple Attributes

In this post, we’ll explore a method to allow a user to select an item from a drop-down list and then use formulas with the VLOOKUP function to retrieve multiple values from the related item table.

Objective

Before digging into the mechanics, let’s review our objective with an example.

We would like to allow our user to select items from a drop-down list, and then have Excel automatically retrieve multiple values into separate columns for the selected item, as illustrated below.

20141002-a

Once the user selects an ItemNum from the drop-down, Excel retrieves the related attributes (Description and Price) from a related items table.

Details

There are several moving parts to this solution, and we’ll take them one at a time. Overall, the drop-down list is created with the Data Validation feature, and provides the user with the list of choices based on the item table. Once the user selects an item from the drop-down, Excel formulas populate the price and description columns with the VLOOKUP function. The steps that follow demonstrate:

  • How to store the items in a table
  • How to create a named reference
  • How to create the drop-down with the data validation feature
  • How to retrieve the item attributes with the VLOOKUP function

Let’s do this.

Store the Items in a Table

The first step is to store the items in a table. The main benefit here is that the table will automatically expand to include any new items added in the future. To store the items in a table, use the Insert > Table ribbon icon. (Note: Tables were first available in Excel 2007 for Windows.)

We want to assign our table a descriptive name, so rather than using the default table name of Table1, we’ll change it to tbl_items with the Table Tools > Design > Table Name ribbon field.

The resulting items table, named tbl_items, is shown below.

20141002a

Create a Named Reference

In order to create our drop-down list based on the ItemNum column of the tbl_items table, we’ll need to set up a custom name. To do this step, simply select the ItemNum column, excluding the header, and then open the Name Manager (Formulas > Name Manager).

In the Name Manager dialog, click the New button. In the resulting New Name dialog, set the Name to dd_items (or any other preferred name) and ensure that the name refers to the table column tbl_items[ItemNum] as shown below.

20141002b

Click OK and then close the Name Manager dialog.

Create the Drop-Down

We’ll create the drop-down with the Data Validation feature. Simple select the cell that should contain the drop-down, and then the Data > Data Validation icon. We want to Allow a List equal to dd_items, as shown below.

20141002c

If you’d like your user to add multiple items, as we’ve done in the first animation above, it is convenient to store the input range in a table as well. That way, the Table will automatically carry the drop-down into any new rows.

Retrieve Attributes with VLOOKUP

Once the user selects an item from the drop-down, we’d like Excel to retrieve the related attributes from our items table (tbl_items). There are a wide variety of ways to accomplish this part, and we’ll use a basic VLOOKUP function. If you have a preferred approach, please share by posting a comment below.

In the Description column, we’ll ask the VLOOKUP function to look up the ItemNum in the items table and retrieve the related description with the formula below.

=VLOOKUP([@ItemNum],tbl_items,3,0)

Where:

  • [@ItemNum] is the lookup value, the item num in our input table. (If you aren’t using an input table, you can use a traditional cell reference, such as A1.)
  • tbl_items is the lookup range, the items table
  • 3 is the table column that has the value we wish to return, the 3rd column, the description column
  • 0 tells the function to perform an exact match of the item num

Optionally, if we wanted to return an empty cell instead of an error, we could wrap the function in an IFERROR function, as follows.

=IFERROR(VLOOKUP([@ItemNum],tbl_items,3,0),"")

Where:

  • VLOOKUP(…) returns the related description, or #N/A if not found
  • “” tells Excel to return an empty text string if the VLOOKUP is an error

We use a similar formula to return the price from the 2nd column, and we are all set. Now, the user can select an item num from the drop-down and Excel automatically retrieves the description and price from the item table, as shown below.

20141002-a

If you prefer other approaches to the steps presented above, please share! Post a comment below…thanks!

Additional Resources

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

188 Comments

  1. Alison on January 20, 2015 at 3:53 pm

    Hi,

    I believe that I followed your instructions correctly. However, I continually get an error when I try to enter the VLOOKUP function. This is what I am entering in for the formula:
    VLOOKUP([@code],insurance_policies,2,0)
    Where “code” is Column A of the spreadsheet and “insurance_policies” is the name of the table created.
    Yet, I continually get this error “The name that you entered is not valid. Reasons for this can include: name does not begin with a letter or an underscore, name contains a space or other invalid characters, the name conflicts with an excel built-in name or the name of another object in the workbook.”
    I changed the name of Column A in table manager to see if that worked and I still got the same error. Where did I go wrong?

    Thanks!

    • jefflenning on January 20, 2015 at 4:02 pm

      Alison,
      My best guess is one of two issues. Either you are running Excel 2007, and if so, try [code] instead of [@code]. You can select the related cell instead of typing it and Excel should populate it with the correct reference. Or, the table name insurance_policies is not valid. To confirm the name, select any cell in the table and then use the TableTools > Design > Table Name field on the Ribbon to confirm.
      Hope this helps!
      Thanks
      Jeff

    • megan on March 16, 2015 at 9:14 am

      Alison I had a similar issue and I was using Excel 2013. I dropped the @ and made sure I was referencing the dropdown cell.
      Hope that helps.

      • jefflenning on March 16, 2015 at 10:00 am

        Thanks for sharing!

    • SV on March 6, 2017 at 6:43 am

      The reason you’re getting the error is because the element ‘[@ItemNum]’ in the above example only works if you’ve created an input table, which this tutorial doesn’t teach you to do, so your best bet is to use the cell reference that you are looking up,
      as suggested in the tutorial:

      [@ItemNum] is the lookup value, the item num in our input table. (If you aren’t using an input table, you can use a traditional cell reference, such as A1.)

      • Cal on March 10, 2020 at 12:27 am

        SV,

        Regarding, ” ‘[@ItemNum]’ in the above example only works if you’ve created an input table, which this tutorial doesn’t teach you to do, so your best bet is to use the cell reference that you are looking up,” Are you saying that this tutorial is incorrect by using @ItemNum and needs use the cell reference that is being looked up, since the tutorial doesn’t teach how to create an input table?

        Thanks!

  2. Dawn on January 30, 2015 at 8:55 pm

    I was able to get this to work but I am wondering how I would do it based on two drop downs. Example being I have one drop down that has 1/2″ (or 3/4″, 5/8″) and the next has 12# (or 14″, 16″) they can mix and match but depending upon the two chosen I would like to output the data that corresponds to it in the chart. Currently, I have it in one cell as (1/2″ 12#, 1/2″ 14#, 1/2″ 16#) and (3/4″ 12#, 3/4″ 14#, 3/4″ 16#) and so on. It would make for a more efficient drop down list if they were two separate lists. Please advise.

    • jefflenning on April 13, 2015 at 2:31 pm

      Dawn,
      Sounds like you are trying to perform a two-column lookup. If the value you are trying to return is numeric, such as amount, quantity, or inches, then I’d go with the SUMIFS function. Otherwise, you can use a helper column with CONCATENATE. Both of these approaches are described here:

      https://www.excel-university.com/vlookup-on-two-or-more-criteria-columns/

      Thanks
      Jeff

  3. Greg Perry on February 11, 2015 at 8:27 am

    Hi Jeff,
    Thank you for the tutorial, I found it very helpful. This Is exactly what I have been trying to do, well mostly. I have two questions.

    1. In my item description cell after the vlookup function is entered I recieve and error #N/A. I think this is because the first cell of my item code column is blank. I need it blank because I do not want a value in it until I select which one. once I select a value it auto populates the item description correctly. Is there any way I can get rid of this error and just have the item description blank as well until i select an option in the item code drop down list.

    2. My second question may be a little more difficult. I want to auto populate the item code cost as well, but with a hitch. I have three different billing rates depending on the customer,(subsidized,non-subsidized, and external) so three different cost columns. I created another drop down with the three customer types. Can I tie it in so that I first select the customer type, then the item code and have it auto populate with the correct item code price?
    Thank you!
    Greg

    • jefflenning on February 11, 2015 at 4:06 pm

      Greg,

      To handle issue 1, I’d suggest using the IFERROR function to return “”…this is illustrated in the final formula of the blog post:
      =IFERROR(VLOOKUP([@ItemNum],tbl_items,3,0),””)
      It is also in the sample file in case you want to check it out in a working Excel file.

      To handle issue 2, I’d suggest using the MATCH function because it can figure out which column has the value to return based on your customer type label. You can use the MATCH function as the third argument of the VLOOKUP function or use the powerful INDEX/MATCH combo instead. This combo is discussed here:
      https://www.excel-university.com/how-to-return-a-value-left-of-vlookups-lookup-column/

      Hope this helps!

      Thanks
      Jeff

  4. frank Sloan on February 13, 2015 at 1:13 am

    Jeff
    Brilliant

    But!

    Sometimes. The price will need updating

    If this happens then all entries in the past will be wrong

    Is there a technique whereby data fields which are liable to updating

    Such as prices or discounts or tax rates

    Can still be used but with the historic data not been lost for reporting purposes

    Many thanks your insights to excel are very helpful and practical

    Frank

    • jefflenning on February 13, 2015 at 8:36 am

      Frank,

      One possible approach is to store the changing price values in a table that includes dates. For example, in a table named Archive, the columns would be DateFrom, DateTo, ItemNum, and Price. Then, you store an archive of all historical and current prices. To retrieve the price, instead of using VLOOKUP you use SUMIFS with comparison operators. For example:
      =SUMIFS(Archive[Price],Archive[DateFrom],”<="&A1,Archive[DateTo],">=”&A1,Archive[ItemNum],B1)
      This way, the transaction date in A1 has be be greater than the DateFrom value and less than the DateTo value.

      Hope this idea helps!

      Thanks
      Jeff

  5. Lee on February 13, 2015 at 9:44 pm

    “If you’d like your user to add multiple items, as we’ve done in the first animation above, it is convenient to store the input range in a table as well. That way, the Table will automatically carry the drop-down into any new rows.”

    I want the table to carry the drop-down into any new rows.

    However, I am not getting how that works.

    Could you please expand on that? Thanks!

    • jefflenning on February 13, 2015 at 9:51 pm

      When you apply data validation to an entire table column, any new table rows added to the bottom of the table will inherit the data validation drop downs. This is demonstrated in the sample excel file available for download. Please feel free to check out the sample file for reference and also to hit me back with specific errors and I’m happy to help.
      Thanks
      Jeff

      • Lee on February 15, 2015 at 6:45 pm

        I think I have “the table to carry the drop-down into any new rows” part working. Thanks!

        Is there any way to make a “list-plus”? By that I mean the person can pick something from the list or type something not on the list.

        • jefflenning on February 15, 2015 at 10:13 pm

          Excellent update, glad you got it! To allow a user to override the drop-down choices and type in any value, change the data validation error alert style from “error” to “information” or “warning” and you’ll be all set.

          Thanks
          Jeff

          • Lee on February 24, 2015 at 6:51 pm

            Is there any way to make the drop down “list-plus” without generating an error?



          • jefflenning on February 24, 2015 at 7:03 pm

            To allow the user to enter a value not in the drop down, change the data validation alert style to Information. To prevent the lookup formula from showing an error, use the IFERROR function. Hope it helps!



          • Lee on February 25, 2015 at 4:57 pm

            How exactly would IFERROR be used here? Thanks!



          • jefflenning on February 25, 2015 at 5:03 pm

            You would place the IFERROR function around the VLOOKUP function, for example, =IFERROR(VLOOKUP([@ItemNum],tbl_items,3,0),””).
            This is demonstrated in the sample file…please feel free to download it and check it out.
            Thanks
            Jeff



  6. Nick on February 19, 2015 at 6:32 am

    Hello, great tutorial but this isn’t working for me?

    I have the same issue as Alison in comment 1. Tried the VLOOKUP with and without the @ but no luck with either.

    Is there any email I can send a screenshot to to show you?

    My forumla is this.. =VLOOKUP([@ItemNum],Tbl_Items,2,0)

    Thanks

    • jefflenning on February 20, 2015 at 9:57 am

      Nick,
      This formula assumes that the lookup value, the first argument of the VLOOKUP function, is being stored in a table. If not, then, instead of using [@ItemNum] as the first argument you would use a traditional A1-style reference.
      Hope this helps!
      Thanks
      Jeff

  7. Cameron on March 17, 2015 at 2:47 pm

    Hi Jeff,

    I’m trying to use a drop down list to change the data in multiple cells when I choose an item from the list.
    For example:
    I have a drop down list in cell F1 containing Quotes, Work Order, Packing List, and Invoice. When I choose one of those options from the list, I’d like cell D5 to reflect the data “Quote #:” or “Work Order #:”. Any tips?

    Thanks,
    Cameron

    • Cameron on March 17, 2015 at 2:50 pm

      I’m using this formula:=VLOOKUP(F1,G3:H6,4,FALSE). The G3:H6 refers to the list of possible outcomes for values of D5. Please help. Thanks.

      • jefflenning on April 13, 2015 at 2:14 pm

        Cameron,
        I think you are soooo close! I think you just need to update the third VLOOKUP argument…change it from a 4 to a 2. This is the argument that tells VLOOKUP which column has the value you want to return. Since your lookup range G3:H6 has but two columns, Excel will return an error with 4 but should return the expected result with 2.

        Hope it helps!

        Thanks
        Jeff

  8. Rudy on March 23, 2015 at 12:53 pm

    Jeff,

    Thanks for posting! This is exactly what I’ve been researching.

    I’m having some difficulties using the VLOOKUP formula because my source data (table array reference) is located in a different worksheet from the one where I want the data to populate.

    Whenever I type in the VLOOKUP formula, it seems to me that I get an error because of the table column reference is on another worksheet. When I tried input the name of the worksheet and the table column number, I generate an error unless I type in the worksheet name and the column letter + column number. (e.g., SourceData!H8 must be input rather than SourceData!8). Either way, the formula does not yield my desired result.

    What, if any, suggestions do you have?

    Thanks!

    • jefflenning on March 23, 2015 at 3:24 pm

      Rudy…I see in your example above you used SourceData!H8…the trick to using column only references is to use it like this: SourceData!H:H. Give it a try, and I think you’ll be set.
      Thanks
      Jeff

  9. Amanda on March 26, 2015 at 9:40 am

    Hi Jeff

    I am using the formula as instructed above, but when a selection is made from the drop down, the vlookup does not refresh automatically, only when the vlookup is displayed on the function toolbar, and manually hitting “enter”… any suggestions on how to make the vlookup poplulate automatically?

    Thank you

    • jefflenning on March 26, 2015 at 11:47 am

      Amanda,

      I would double-check that the worksheet is set for automatic calculation… Formulas > Calculation Options > Automatic. Once it is set to automatic, the VLOOKUP as well as other worksheet formulas should automatically update when a dependent cell value is changed.

      Thanks
      Jeff

      • Adam on July 3, 2015 at 6:48 am

        Hi I would like to make a quote sheet that I can input date such as 3mm MDF and I would then like one of the other cells to automatically update with the price of that particular sheet

        Could you point me in the right direction of anything that I can do to make this work?

        I am working on a MAC

        • Kurt LeBlanc on August 17, 2016 at 9:22 am

          Hey Adam,

          I’m sorry, but I don’t have Excel for Mac to help you…But it sounds like this is the logic you should keep looking into to solve your issue:)

          i hope you find your answer,
          Kurt LeBlanc

  10. Robert on April 8, 2015 at 12:13 am

    Hi Jeff;
    Thank you for the above details, they are very helpful.
    i am trying to use your Vlookup to retrieve some items from inside my table. it is not working 🙁
    i am using Excel 2013.
    my table name is: daysinamonth
    the first column from where i am taking the name of the month is called “monthlist” this is the header
    the cells below “monthlist” have the name of the months and the whole chunk is called “mymonth”
    my drop down list (in another sheet) is reading from the mymonth list.
    the formula used is: =IFERROR(VLOOKUP(“@myinputlist”,daysinamonth,6,FALSE),”error”)
    and it is giving me error all the time.
    i tried: =IFERROR(VLOOKUP(“@mymonth”,daysinamonth,6,FALSE),”error”)
    also it is giving me the same error.
    can you help please?
    Robert

    • jefflenning on April 9, 2015 at 12:31 pm

      I think you just need to change “@mymonth” argument to point to the cell that you are looking up, that is, the cell that has the value to search for in the monthlist column. For example, if the name of the month is stored in A1, then update the argument from “@mymonth” to A1.

      Hope it helps!

      Thanks
      Jeff

      • Robert on April 14, 2015 at 5:00 am

        Jeff;
        The thing is i cannot use the A1 name as what i am trying to fill is a table of 37 rows.
        So each time i need to select the drop down list from cell A1 (for row one) and vlookup some fields.
        then will go and select the drop down list from cell B1 (for row 2) and vlookup some fields.
        and so on….
        Hope that this clarify things;
        Rob.

        • jefflenning on April 14, 2015 at 12:56 pm

          Robert,
          Ah…I see…thanks. In that case, the lookup value is in a table, so, the structured table reference for the current row would include square brackets around the column name. For example, if myinputlist is the column header, use [@myinputlist] instead of “@myinputlist” and if mymonth is the column header then use [@mymonth] instead of “@mymonth” and so on. The thing is, Excel should automatically insert the correct structured table reference for you if you click the desired table cell while writing the formula.
          Hope it helps!
          Thanks,
          Jeff

          • Robert on April 22, 2015 at 1:33 am

            🙁 it is not working at all. i have even changed all the field’s names to match your excel example.
            [@itemNbr] is not working
            [ItemNbr] is not working
            itemNbr is not working.
            can i email you my excel template to check please?
            i am really frustrated 🙁

            thank you for your Help



  11. William on April 8, 2015 at 9:36 am

    Jeff,

    I’m trying to follow your example, but I’m not sure what I’m needing to do has been explained.

    I want to have several different drop down menus all looking up on the previous drop down selection. So if we use your example, let’s say there are multiple item numbers per Description. After the description is selected in the drop-down, then I only want that specific list of item numbers to populate in the next drop down.

    Thanks!
    William

  12. Ben on April 14, 2015 at 5:48 pm

    I was also struggling to get this working in Excel 2010 and eventually discovered that the VLOOKUP function cannot look up values to the left of the lookup column, so make sure your lookup value (ItemNum in the example) is the left-most column.

    The workaround is to use the INDEX MATCH function which is more versatile. See here:
    https://www.excel-university.com/how-to-return-a-value-left-of-vlookups-lookup-column/

    • jefflenning on April 14, 2015 at 8:50 pm

      Ben,

      Ah…yes…thanks for the assist!

      (Updated your link to point to the related EU post)

      Thanks
      Jeff

  13. Jesse on May 13, 2015 at 2:07 am

    Would this work with data that has multiple prices for different states.. for example:

    NSW SA NT
    chips1 $1 $1.2 $1.8

    but i want the data validation to be that you use the different states and then the prices will just show for each item rather than it listing all the prices

    thanks

  14. Jeff on June 9, 2015 at 12:12 pm

    How can I use the drop-down option in conjunction with the Index/Match Function in order to return multiple values? For example, I have 7 categories (Waves 1-7) and within each Wave, I have 4 stores. I want to select my name from the first drop-down, the Wave # from the second drop-down and have the formula return all 4 stores from the selected Wave vertically. Is that possible?

    • Kurt LeBlanc on August 16, 2016 at 12:27 pm

      Hey Jeff,

      I think all you need to do when you enter the formula is select it and 3 more cells and fill the formula down. Make the formula return a list and each cell should have a diffeernt value:)

      Let me know if this works out,
      Kurt LeBlanc

  15. Koh Melissa on June 17, 2015 at 5:33 am

    Dear Jeff,

    Thank You so much for sharing. I’ve downloaded sample spread and i found this formular,
    =IFERROR(VLOOKUP(tbl_detail[[#This Row],[ItemNum]],tbl_items,3,0),””)
    may i know what does it mean by [[#This Row] ?

    • Kurt LeBlanc on August 17, 2016 at 9:17 am

      Hey Koh,

      I’m not sure where you found that formula, but I don’t think it’s right…
      The VLOOKUP formula has arguments of (lookup value, table, nth column, match type)
      But “[[#This Row],[ItemNum]] isn’t even a valid reference.

      I hope that gives you the answer you need,
      Kurt LeBlanc

  16. adam on June 21, 2015 at 11:55 pm

    Hi I am trying to follow these instructions but i am using a Mac, I assume the instructions are different for the Mac but I cannot find any specific on the net

    Can you help at all?

    • Kurt LeBlanc on July 1, 2016 at 6:01 am

      Adam,

      I’m sorry but neither Mr. Jeff or I have a copy of Excel for Mac to know how to help you.

      Thanks,
      Kurt

  17. Tom on June 29, 2015 at 4:19 pm

    Hi Jeff

    I admire your patience for all the questions! I’d appreciate it if you could answer one more. I’m using Excel 2007 and got the formula to pull back data, but it’s only pulling the corresponding value from the row where the formula is entered. e.g. the formula in cell B2 is ignoring my selection from the drop down (A2) and pulling back a value from cell I2 (When it should I7)

    Target Sheet
    Ingredient Cost per g/ml Quantity Cost (Pence)
    Flour (self raising) 0.24 3 0.72
    Sugar (granulated) 14.58333333 4 58.33333333

    Source Table
    Ingredient Size of pack Price Cost per g/ml
    Sugar (brown) 1000 249 0.249
    Sugar (muscovado) 1000 240 0.24
    Eggs 12 175 14.58333333

    Formula
    =IFERROR(VLOOKUP(Ingredient,Recipe,4,0),””)

    Ingredient is the look up column and Recipe is the table name

    • jefflenning on June 29, 2015 at 8:37 pm

      My best guess at this point is that you’re telling Excel (unintentionally) to use an implied intersection…which is simply a fancy term that means when you name or reference an entire column, such as naming a column Ingredient, a formula that references the name will reference the cell in the column that is in the same row. Thus, if you name column I Ingredient, VLOOKUP(Ingredient…) tells Excel to reference the cell that is in the Ingredient column and the same row as the formula. One possible solution is to use a specific cell reference such as I7, or VLOOKUP(I7,…).
      Hopefully that addresses the issue and resolves the problem…thanks!
      Thanks,
      Jeff

  18. renier smit on July 6, 2015 at 4:24 am

    hi, i wonder if anyone can help me please. i managed to get my drop down list right (A1 down to A10) options are “quoted” and “invoiced” in column B1-B10 i will put my own values. the question i have if what is the exact formula should i want a quick look at the end result for example on sheet 2 i just want to see what are the total invoiced and what are the total quoted. i hope my question make sense, i would appreciate if someone can assist me.

    thnx in advance.

  19. Debbie Maiella on July 6, 2015 at 12:47 pm

    I’m trying to make a schedule whereas the first column of my drop down consists of all available shifts. The second column is the number of working hours that will worked on that shift. In the problem cell, I want the cell to look at what shift has been selected, and display the number of work hours. There will be multiple days in the range, so in full swing, I would like it to add together all the work hours selected in that week so we can keep track of hours vs. budget.

    Thank you!

  20. David Dixon on August 7, 2015 at 5:16 pm

    Hi Jeff,
    Don’t know if you’re still monitoring this site, hopefully you are as I am having great difficulty in getting this to work.
    I have created a range of data S7:W79
    Row 7 are headers.
    My Drop Down is B6.
    I want to read data from column T into B13
    I want to read data from column U into C13
    I want to read data from column V into D13
    I want to read data from column W into E13

    I have tried
    Insert > Table > $S$7:$W$79 tick My table has headers > OK.
    Table Tools > Design > Re-name to tbl_Section > Return (Header S7 titled SectionCode)
    Select S8:S79 > Formulas > Name Manager > New > Re-name to dd_Sections refers to =tbl_Section[SectionCode] > OK > Close
    Highlight B6 > Data > Data Validation > Allow:List Source:=$S$8:$S$79 > Confirm B6 contains list S8:S79
    Highlight B13 > =VLOOKUP([@SectionCode],tbl_Section,2,0) > this did not work > Change to =VLOOKUP(tbl_Section[SectionCode],tbl_Section[#All],2,0) this results in B13 being populated with data from T13 and cannot be changed, no matter what is selected from the Drop Down list

    Really appreciate if you could explain to me where I’m going wrong with this?

    Kind regards

    Dave

    • jefflenning on August 10, 2015 at 8:09 am

      David,
      It looks like you were really close! I recommend modifying your VLOOKUP function in B13 to this:
      =VLOOKUP(B6, tbl_Section, 2, 0)

      The first argument of the VLOOKUP function is the lookup value, the value you are trying to find in the first column of the tbl_Section table. Since your drop down is in B6, that is going to be the lookup value argument.

      Hope this helps!

      Thanks,
      Jeff

  21. Karen on August 13, 2015 at 9:57 am

    Hi Jeff,

    Excellent information! However, I was wondering if it is possible for VLOOKUP to return multiple rows of information as opposed to a single row? For instance, if I wanted to select an author from a dropdown list, then have 10 book titles show up, with their genre and cost. Is that doable with VLOOKUP?

    Thanks,
    Karen

    • jefflenning on September 3, 2015 at 10:06 am

      Karen,

      The VLOOKUP function isn’t really designed to do that, and although there is probably a clever way to write a formula that does such a task, there are some easier options to consider. For example, if you have Excel 2013 for Windows, you could have the user select the author from a Slicer instead of a drop-down cell, and then the table will immediately filter to show all related titles, genre, cost, and any other attributes stored in the table. To do this, convert the data range into a table by selecting any data cell and then select the Insert > Table ribbon icon. Then, Insert a Slicer for the author column. Now, when you pick an author from the Slicer the table is immediately filtered for the selected author.

      If you are on Excel 2010 for Windows instead of 2013, then you could create a PivotTable report to display the desired attributes and then use a Slicer to filter the report for the selected author. In Excel 2010, Slicers can’t be used to filter a table, but, they can filter a PivotTable report.

      Hope it helps!

      Thanks
      Jeff

  22. Kevin on September 10, 2015 at 6:58 am

    Hi Jeff: I have four drop downs; Selecting one drop down for Years, second for four quarter in each year selected, third is services offered in each quarter after selected quarter and finally status of attended service or not. Can a summary sheet show all the data on relevant selection of drop downs. Please advise solution. Thank you so much. Greatly appreciated.

    • jefflenning on September 17, 2015 at 10:31 am

      Kevin,
      If you just want the total on the summary sheet, then, I would recommend using the SUMIFS function to compute the total of all matching data rows based on the conditions found in the four drop downs. If you want to view the details instead, that is, all matching data rows instead of a single total, then I would probably use Slicers instead of drop downs. Starting with Excel 2013 for Windows, Slicers can operate on (filter) tables. So, I would store your data in a table (Insert > Table), and then set up slicers for the desired columns, and then as the user makes selections in the slicers then the table will filter accordingly, displaying all matching data rows. Alternatively, the detail table is stored on a data sheet, and then you could create a PivotTable on the summary sheet and use Slicers to help the user filter the summary report. Slicers can operate on PivotTables in Excel 2010+ for Windows.
      Hope these ideas help!
      Thanks
      Jeff

      • Kevin on September 18, 2015 at 8:44 am

        Many thanks Jeff. Greatly appreciated.

  23. Pauline Chua on September 15, 2015 at 12:55 am

    Hi Jeff,

    This article really helped me alot! However, I have a few queries.. I would like to do up an equipment list with select-able quantities. What I mean is that, there will be a drop down menu to select the type of equipment I would like, and the quantity will be dependent on the type of equipment chose.
    So let’s say, I have 5 of equipmentA and 10 of equipmentB, when I select equipmentA, on the quantity side, I will only want to see 1 to 5, but when I select equipmentB, I would want to see 1 to 10. Is there way to do this?

    And also, excel do not allow spaces when defining names, so I read about using vlookup to create a table but I’m lost as to how it works.
    Can you help me out?

    Thanks!!

    • jefflenning on September 17, 2015 at 10:14 am

      Pauline,
      A couple of ideas come to mind. One way, which I think is pretty cool, is to use Data Validation to create a drop-down for the Equipment column. Then, for the quantity column, use Data Validation to allow a Whole Number between 1 and X, where, X is the allowable quantity. You could use a lookup function like VLOOKUP to retrieve the allowable max quantity from a related table.

      If you prefer to have the quantity cell be a drop-down instead, then, you could store the quantities in a related table but the thing is, you’ll need to list out each quantity individually, that is, have say 5 rows for EquipmentA that show 1, 2, 3, 4, 5. This is a bit tedious, especially if the max allowable quantities change frequently. That’s why I think I personally prefer the first option above as it would be easy to maintain over time.

      Hope this helps, and best of luck!

      Thanks
      Jeff

  24. Alex on October 5, 2015 at 9:53 am

    Good afternoon Jeff,
    I finally got my vlookup working, but now i want to look up info on sheet one with the information entered on sheet two. here is my formula that worked on sheet 1.

    =vlookup(a10,a4:a5:b4:b5,2,false)

    any help you have is appreciated.

    • jefflenning on October 8, 2015 at 11:48 am

      Alex,

      Sure thing…you can tell Excel which sheet should be referenced by prepending the sheet name (inside single quotes) with an exclamation mark to the range reference. For example, if your lookup value in stored on a sheet named Sheet 1 in cell A10, then, you’d use ‘Sheet 1’!A10. Likewise, if the lookup range is stored on a sheet named Sheet 2 in a range A4:B5, then you’d use ‘Sheet 2’!A4:B5. This is how you can control which sheet is used for each argument…hope it helps, and best of luck!

      Thanks
      Jeff

  25. Alison on October 12, 2015 at 11:54 am

    Hi Jeff,

    I hope I’m not repeating the same question as everyone else, but I’m having a problem with this. Here is my situation…
    I have several tabs with vendor information (name, email, phone, etc), I am trying to create a drop down for each vendor that will automatically pull in the email and phone information off of the name. I thought I had everything working, but the vlookup is not validating off of the vendor name.
    I am using =vlookup(Electrical!A:A,tblElectrical,2,0). I’m thinking the problem lies in that I am not able to use the @columnname for some reason.
    On a separate note, I realize that I would have to create a vlookup for the other columns…unless there is a way to pull in multiple columns in one vlookup?

    Thank you for your help and this awesome tutorial!

    • Alison on October 13, 2015 at 8:36 am

      Hi Jeff,

      I figured out one part of my issue. But now have another one that I’m not sure is related to this thread. Please feel free to delete if not applicable…

      On the same workbook, I have about 30 vendor tabs with data (name, email, contact, phone). I want to create a form (per se), that allows the user to select the vendor type and once that is selected, the drop down will list the appropriate vendors to choose from (ie, plumber will only show the plumbers we have listed); I have these two drop downs created, but now I want to vlookup the other information to pull into the form. If I use vlookup and if then statements, the code will be huge because I have to include all 30+ vendors. Is there an easier way to bring in the additional data?

      Thanks in advance!

    • jefflenning on October 30, 2015 at 11:29 am

      Alison…I believe you are super close! Instead of the first VLOOKUP argument being a column, such as A:A, you want it to be a single cell value, the vendor name that you are trying to look up. So, assuming your vendor name is stored in cell A1, then you would want to modify your formula to something like this:
      =VLOOKUP(A1, tblElectrical, 2, 0)
      With that tweak, I think you should be good to go…hope it helps…and best of luck!
      Thanks
      Jeff

  26. Gram on January 3, 2016 at 4:02 am

    i trying to do a drop down list where if you put for example A in A1 then A2 would auto input B and A3 input C and so on and if I put B in A1 then A2 would input C and so on etc can this be done

    • Kurt LeBlanc on October 19, 2016 at 9:22 am

      Hey Gram

      I figured it out:) It takes a little work since Excel doesn’t recognize the alphabet as a data type.

      I created a table of the alphabet named “alphabet.” Then you’ll have your drop-down somewhere. Below it I used an INDEX with MATCH for the row number. The MATCH will look at the cell above, in the “alphabet, 0 for exact match. Add 1 to it to return the next letter in the sequence:

      =INDEX(MATCH(A1,alphabet,0)+1)

      Then I wrapped the formula with IFERROR and depending if you want the sequence to start over whenever it reaches “z,” have the second argument be “a,” or if you want the cells to be blank after “z,” have the second argument as “”.

      =IFERROR(INDEX(MATCH(A1,alphabet,0)+1),”a”)

      =IFERROR(INDEX(MATCH(A1,alphabet,0)+1),””)

      Let me know how that works out,
      Kurt LeBlanc

  27. Ross Day on January 15, 2016 at 9:06 pm

    I have used this and it works perfectly. I am now trying to go one step forward. I have a list of plants, plant sizes, and pricing that i want to use. I would like to select from a drop down list a plant name, then in the next cell have the size option available in a drop down, and once that is chosen the price automatically populate. I can email the sheet or a jpeg if that would help. I’m not sure i’m describing this 100% but hoping i can get some help. Thank you!

  28. David Watkins on January 18, 2016 at 6:52 pm

    Thank you so much for this information. I fear that I am trying to do something that cannot be done. I have a set of worksheets that compute the costs associated with a construction job, based on the information in an “ItemCost” worksheet. I have recently run into a situation where I need to maintain 2 different ItemCost worksheets. I don’t want to duplicate all the various other worksheets that reference ItemCost (too complicated to maintain), but thought that if I could have 2 ItemCost worksheets (say 1 and 2), then I could use the VLOOKUP function to pick the cost sheet that should be used, and then substitute that worksheet name into the cell formulas.

    So, for example, if I have a worksheet titled PRICE1, and want to retrieve the value that is in cell A1, normally I would just reference =’PRICE1′!A1

    I have played around with this, and have gotten as far as: =”‘”&VLOOKUP(B6,Price_Table,2,0)&”‘!A1″
    where B6 is the cell where I have picked the desired table choice from the drop down as you describe above.

    What I get back is the desired string, but displayed in the cell as a string, instead of being used to reference the contents of the cell in the other worksheet.

    So, when I select an item from the drop down, in the VLOOKUP cell, it displays: ‘PRICE1’!A1 just like I want it to (so I know it is retrieving the information I want) – it just isn’t using that information to reference the defined cell and retrieve the information from that cell.

    I have tried an alternative to this method – having 2 different columns in the ItemCost table – one for each variation in price, and then using the VLOOKUP to return the column identifier (say, “A” or “B”). But I get the same result. The cell displays the string that was built instead of the result of referencing the “foreign” cell contents.

    Is there another level of redirection, or addressing that I have to use in order to force it to use that cell reference, instead of just displaying the string? Or perhaps VLOOKUP is the wrong tool to use?

    Thanks again for all your help developing this information…!!
    ………………….Dave

    • jefflenning on January 19, 2016 at 11:50 am

      Dave…I believe the missing piece of the puzzle here is the INDIRECT function, which converts an ordinary text string into an actual Excel reference. Wrapping INDIRECT around the computed text string function should get you there. Hope this helps!
      Thanks
      Jeff

      • David Watkins on January 19, 2016 at 6:15 pm

        Thanks Jeff….works perfectly…!!

        • jefflenning on January 19, 2016 at 6:44 pm

          🙂

  29. Tess Fullard on February 4, 2016 at 4:23 am

    Hi there,

    Please help. I have a 15 sheet excel 2007 spreadsheet. I have all my data on the 15th sheet.
    On the first 14 sheets are our 14 tenants. The data on the 14th sheet contains menu items in column B and the corresponding cost of each item in column C. I have inserted a table constituting the two columns and rows making up the menu items and their associated costs B4 to C21 (if I include the headers). I named this menu_items.
    I have managed to create a drop down option in column B of each of the tenant sheets but cannot for the life of me work out how to get it to populate the corresponding cost in column C on the tenant sheets.

    Any help you could provide would be hugely appreciated!

    • jefflenning on February 4, 2016 at 12:33 pm

      Tess,
      I have a feeling you are really close, so hang in there. In your menu_items table, I’m assuming that the menu items are in the first column, and the related costs in the second column. If that is true, then a VLOOKUP should do the trick. Here’s how. You’ll use a VLOOKUP function on each of your 14 tenant sheets. Assuming the first menu item selected by the user is stored in cell B1, the related formula in say C1 that retrieves the related cost would look like this:

      =VLOOKUP(B1,menu_items,2,0)

      That formula should lookup the selected menu item in cell B1, within the menu_items table, return the value in the second column (the cost), and 0 means exact match.

      Give this a try…I have a good feeling it will work. Feel free to hit me back if it doesn’t!

      Thanks
      Jeff

  30. Matt on February 22, 2016 at 6:55 pm

    Hi Jeff,

    I’m really struggling to get this to work and I have absolutely no idea why.

    I’m using an input table with the table name of tbl_services , although I’ve tried various alternatives.
    The column header is referred to as Provided

    So I’m creating a VLOOKUP as follows

    =VLOOKUP([@Provided],tbl_services,2,0)

    But whenever I press enter to finish, I get an error that says the name I entered is not valid.
    It lists 3 reasons,
    – the name does not begin with a letter or an underscore.
    – the name contains a space or other invalid character
    – the name conflicts with an in-built Excel reference or the name of another object in the workbook

    I’ve genuinely tried everything. Changing table names, table headers, tried copying the formula from your example to fit it around my requirements. But I’m no Excel expert and it’s proving time consuming. I’d rather my own attempt just work.

    Any ideas?

    Regards,

    Matt

  31. Cindy on February 23, 2016 at 8:09 am

    Hello,

    It seems I’m having the same trouble a few others in the comments had. I have a table named “Reference_Table” on Sheet 2 set up as follows:
    Column A – item number
    Column B – price
    Column C – description (I named this column “packages” )

    The drop down menu is on Sheet 1 Column C. I’d like the prices to auto-populate in Sheet 1 Column M depending on which item in the drop down menu is selected.

    I’m entering this formula into cloumn M: “=VLOOKUP(packages,reference_table,Sheet2!B:B,0)” but keep getting a “#N/A” or “#VALUE” error message.

    I’ve tried replacing the zero with “FALSE” and tried naming the price column and referencing it instead of using “Sheet2!B:B.” Still no luck, please help!

    Thanks a ton!

    • Kurt LeBlanc on July 5, 2016 at 1:16 pm

      Hey Cindy!

      You have the right idea, but are using the wrong arguments. It’s an easy fix:) Instead of packages, you want to look up the item number in the table. Then the column number can be found with MATCH(). You match the name “packages” in the table’s headers. The resulting formula is:

      =VLOOKUP([@Item Number],reference_table,MATCH(“packages”,reference_table[#Headers],0))

      Let me know if that helps!
      Kurt LeBlanc

  32. Jenny W on February 24, 2016 at 2:17 pm

    Hi

    I am receiving the following error message “the list source must be a delimited list, or a reference to a single row or column”

    How can I solve this please? Quite a novice with excel so please speak slowly to me!

    Thanks very much

  33. Omokayode on March 22, 2016 at 2:15 am

    hi jeff,
    i think my situation is also similar to earlier discussions on here, i have two sheets, one with a schedule based on morning,afternoon and night shift for team leaders and i’m hoping to populate the shifts done daily on the other sheet. how do i make the data auto populate based on changing a particular cell to morning,afternoon and night shift. Also, there are Associates attached to each Team lead and i’m also trying to have a count of associates working daily under the Team Leaders. i hope my explanation isn’t too vague to understand.

  34. Casey on March 25, 2016 at 12:51 pm

    Where do I enter the =Vlookup funtion? Am I entering it in the same cell that the drop down is in, or am I entering it somewhere in my reference table I’m confused? I have a table called discrepCodes. I selected the cells underneath the first column which is called “Descriptions” and created a name (just used yours, dd_items). The table has two columns. The description column and the codes column and I want when I select the description in the drop down for it to populate that cell on another sheet and then simultaneously add the code in the cell right next to it. I don’t know what I’m doing wrong but I enter: =VLOOKUP([@Description],discrepCodes,2,0). I have tried it without the @ and I still get “Name you entered is not valid”, or “the formula you typed contains an error”. Thoughts?

    • Kurt LeBlanc on October 19, 2016 at 8:36 am

      Hey Casey,

      It looks like your formula contains a circular reference in that it looks at the same cell your formula is in. The formula should be in the cell you want to display the result. The formula will be the cell’s “stored value” while the result will be the “displayed value.” Try moving your formula to another cell and let me know if that works.

      Kurt LeBlanc

  35. Patrick on April 5, 2016 at 3:14 pm

    Jeff, nice work. Im looking for a multi dependent dropdown list solution. Here is the problem. I have an Electrical Components list with headers Model, Voltage, Wattage, etc. Each model can have none or multiple values for any header eg Voltage could be 12, 24, 120 for model A, 120, 240 for model B, no entries or model C etc. What I want is to have in Col A (Model) a dropdown of models (easy), then in col B (Voltage), a dropdown list that would have entries 120 and 240 if Model B chosen or 12,24,120 if Model A chosen or nothing if Model C chosen. Similarly the column for Wattage and other items that describe the component. I have looked at basic dependent dropdowns using indirect and vlookup and get these working but this is beyond any examples I have seen online so far.

  36. Rob on May 2, 2016 at 5:54 am

    Hi Jeff,

    You did exactly what i need, but i tried most of my day to get it working, and it still doesnt work.

    Sheet 1, Collumheads Day of month, day of week, schoolname,phonenummer, contactperson
    Sheet 2 collumhead, naam_school; phonenr_;visitingadres_zipcode_contactperson

    After selecting the right school from the drop down (that works) in sheet 1 i would like to have all the other information filled in from the same list.

    The function i use to get the phonenr: =VLOOKUP([@Schoolnaam];Scholen_tbl;2;FALSE)

    But i get a #N/A

    Hope you can help

    • Kurt LeBlanc on July 27, 2016 at 9:50 am

      Hey Rob,

      It looks like you have the right idea, but one little mistake. Use commas instead of colons to separate the arguments:)

      Let me know if it works for you after please
      Kurt LeBlanc

  37. Gbenga on May 13, 2016 at 6:16 am

    You are doing great Jeff!
    Please I need your help on this:
    I’m working on generating report sheet for each student in a class. I have the students performance on each subject on separate worksheet which I named with the subject name. On the report card worksheet, I created a drop down list of all the children in the class. My aim is to select a child’s name in the drop down and all the child’s score in each subject is pulled from all the worksheet and populated into the corresponding table created on the report sheet worksheet.

    • Kurt LeBlanc on July 22, 2016 at 2:09 pm

      Hey Gbenga,

      It sounds like a job for the SUMIFS() or VLOOKUP() function with INDIRECT()! You can have it return data that meets your criteria then INDIRECT() can make text a valid reference (for your subject to become a sheet name.

      Let me know if I can be of more help:)
      Kurt LeBlanc

  38. Patrick on May 20, 2016 at 10:54 am

    Hi – Your articles are very helpful, so I’m hopeful you’ll be able to help me out. I’m a gifted education teacher with 65 students. I offer 40 field trips a year to them, and they can pick which ones they want to go on. I created an excel sheet with their names down the left side, A3, and the trips across the top, Starting with D1. Where they intersect, I placed a number 1 in the cell showing they attended the trip. I need to create a summary of trips for each student. Besides copying and pasting for each student, Can excel do this? I have SOME knowledge of excel. Seems like you’re the guy to ask, based on everything I have read. help? 🙂

  39. Ray Schindler on May 22, 2016 at 3:33 pm

    I appreciate the tutorial, but man oh man am I frustrated. I have been sitting here for hours trying to get this to work to no avail. I give up, my project obviously isn’t going to get done. I’m using Excel 2013, following each and every step, even noting the above comments and it WILL NOT work.

    • Kurt LeBlanc on July 1, 2016 at 9:27 am

      I’m sorry you’re having so much trouble Ray…

      Can you explain your issues with it so that I can help you?

      Kurt LeBlanc

  40. Terry Treman on May 25, 2016 at 11:43 am

    I can not get the sample file to download or open in my browser. Is it still available?

  41. Georgina Lawry on May 31, 2016 at 3:10 pm

    Hello,

    What i am trying to do is create a dropdown box where select a cell and it will populate another 4 or 5 cells next to it.
    Can you email me your contact details so i can send you an example of what i am trying to do?
    This is for a pricing sheet we use.

    Many thanks,

  42. Shihab on July 3, 2016 at 11:43 am

    Hi Jeff, Its awesome to learn a new function. Thanks for explaining in detail.
    I have tried VLOOKUP function to develop a diet chart. The dropdown menu selects the food items and its specified quantity in the first column. Like ‘Chicken wings, 100gms’ and the second to fifth columns shows up the total calories, fat, carbohydrate and protein respectively. Its working perfect. I have extended it down for further meals and items. On the top, the date is given and using the ‘SUM’ function calculates the whole details as required.
    My question is, in a scenario where the user wants to input 150 or 200gms of chicken wings (in the above given example), what modification should I do to get an automated results in the second to fifth columns?

    • Kurt LeBlanc on July 8, 2016 at 8:03 am

      Hey Shihab,

      You should split your columns to have one data type each. That way you can have 2 drop downs and automate the others.

      Let me know if you need anymore help with it:)
      Kurt LeBlanc

  43. Arthur Nemirovsky on July 20, 2016 at 9:58 am

    Dude! Lenning! I was looking for this real quick and google and you came up! I miss you buddy you are truly the excel master. Wishing you and the family well!

    • jefflenning on July 20, 2016 at 10:00 am

      Rofsky! Wow dude, great to hear from you! Hope you are doing well!

  44. Zach on July 27, 2016 at 10:54 am

    Jeff,
    I have been trying to get this formula to work all day. I have a table with a materials column and a price column for each material. I am trying to create a spreadsheet that auto populates the cost from hte table when the drop down of materials is selected. I can’t figure out how to get the cost column to reference the material drop down in the spreadsheet in order to pull the info from the table. Any help?

  45. Wesley on July 30, 2016 at 1:09 pm

    Jeff, thanks for this tutorial. After running through it, I’m very close to a solution for my scenario but revieve a name error in the cell containing the VLOOKUP formula.

    As with the tutorial, I’m matching a dropdown list item to a related value, with Items / values stored in a two-column table on a separate tab.

    Tab name: LookUp Values
    Table name: food_items
    Column 1 Header: Food Item (I have named this column’s values as ‘dd_items’)
    Column 2 Header: Calories

    The formula I’m trying to use (grabbed from a comment above):
    =VLOOKUP(“[@Food Item]”,food_items,MATCH(“calories”,food_items[#Headers],0))

    This is on Excel for Mac 2011. Any help is greatly appreciated.

    • Kurt LeBlanc on August 1, 2016 at 9:07 am

      Hey Wesley

      I don’t have a copy of Excel for Mac, unfortunately, but I think your logic is off. I hope someone else sees this and can help you!

      Kurt LeBlanc

      • Wesley on August 1, 2016 at 11:00 am

        Hi Kurt – I don’t think working on a Mac version of Excel relates to this error. Can you elaborate on your comment about the logic? As mentioned in my comment, I applied a formula construction described in an earlier comment that achieves the same result I’m after.

        • Kurt LeBlanc on August 1, 2016 at 11:25 am

          Hey Wesley,

          The VLOOKUP follows this logic:

          find this value, in this range, and return the corresponding value in the nth column, match type (typically 0 for EXACT MATCH)
          i.e. VLOOKUP(value,range,col #,0)

          I hope that helps!
          Kurt LeBlanc

        • jefflenning on August 1, 2016 at 12:53 pm

          Wesley/Kurt – I’ll also chime in here and say that if you receive a #NAME? error, that typically indicates that Excel doesn’t recognize a function name, structured table reference, or named range. After reviewing your formula, it would appear that the first argument of the VLOOKUP function is enclosed in quotes “[@Food Item]” and since it actually represents a structured reference, you’ll want to remove the quotes…use [@Food Item] instead. Also, as Kurt pointed out, you’ll want to define the 4th VLOOKUP argument as 0 for exact match.
          Hope it helps…thanks!
          Thanks,
          Jeff

          • Weslley on August 1, 2016 at 1:51 pm

            Thanks to both of you — I greatly appreciate the assistance. I’m not very familiar with Excel’s syntax, so your feedback is really helpful.

            I’ve modified the formula to read:
            =VLOOKUP(food_item_table[@[Food Item]],food_item_table[Food Item],2,0)

            The table name is food_item_table. Column one in labels Food Item, and the name for this column’s range of values is source. Column two is a simple list of number values that correspond to the matching cell in column one.

            This edit handled the #NAME error, but results in a #REF error. The table has only the two columns, and this version of the formula I created using the formula builder.

            Based on the information provided, any ideas on where the #REF error could come from?



          • Kurt LeBlanc on August 2, 2016 at 6:00 am

            Hey Wesley,

            I would think its from the range you selected for the second argument. The range needs to be the entire table. Otherwise Excel can’t find the second column when your range is the single column. Excel knows with VLOOKUP to look in the left-most column for the matching value. Try that and let me know how it turns out.

            Kurt LeBlanc



  46. Reed on July 31, 2016 at 11:04 pm

    in example
    *C21 has drop downs
    *When a drop down is chosen-example drop down pulled from AQ6
    *Need value located at AR6 to show in D21.

    How?

    • Kurt LeBlanc on August 2, 2016 at 11:28 am

      Hey Reed,

      I’m sorry, but can you restate the issue? Your description doesn’t follow the example.

      I can help you better then:)
      Kurt LeBlanc

  47. Adrian on August 4, 2016 at 9:55 pm

    Hi Jeff

    I really appreciate your efforts in putting this post and your patience in answering all the questions. I hope you won’t mind answering one more question.

    I want to use a range in lookup value instead of a single cell reference because I am want Description and Price value for ItemNum that I select from the Drop Down, which means if I select a different ItemNum in Drop Down then values for Description and Price shall also change accordingly. In your example the formula can be written in 2 ways:

    1) =vlookup([@ItemNum],tbl_items,3,0) OR
    2) =vlookup(A1,tbl_items,3,0)

    Whereas I want to use something like this =vlookup(A1:A5,tbl_items,3,0) as this will help me to automatically update the values for Description and Price according the ItemNum selected in the Drop Down.

    I hope I have made my question clear to you.

    Thanking you in anticipation for your support.

    • Kurt LeBlanc on August 18, 2016 at 11:20 am

      Hello Adrian,

      VLOOKUP() does not support a range as the lookup value…what you want sounds like just what the blog addresses though:) The two formulas are similar except for the column’s value it returns. This will help you return 2 different values that correspond to a value.

      Let me know if I can be of more help on this,
      Kurt LeBlanc

  48. Reed on August 7, 2016 at 8:11 pm

    I have created two lists.
    First list is used in a cell as drop down items
    When a drop down item is selected i need to show a value from the second list.
    Reed

  49. Joe on August 18, 2016 at 8:05 pm

    In me spreadsheet I have a drop down where the user will have to select yes or no. If yes is selected I want it to add $20 and if no is selected I don’t want anything to happen.

    Example:
    My H row has the drop down and my J row has the dollar amounts for everything else in my sheet. I got everything working fine except this drop down. So when the user selects yes I want it to add $20 to my J row.

    • Kurt LeBlanc on August 19, 2016 at 6:02 am

      Hey Joe,

      Your problem sounds like something the IF() function can handle:) Its logic is: if this is true, do this, else do this

      I would set up yours to say if the drop-down cell=”Yes”, do this plus 20, else just do this

      I’m happy to provide additional help if you need! Let me know how that works out,
      Kurt LeBlanc

      • Joe on August 19, 2016 at 6:06 pm

        Thanks for the reply Kurt,
        To be honest I’m not that great with excel. I have worked hard to get what I have currently, which I’m sure is a breeze for others lol.
        What would be the exact thing that would be needed to input into each cell

        • Kurt LeBlanc on August 20, 2016 at 10:24 am

          No problem Joe:)

          I’m not sure what the exact cell references are for your project, so I will just assume the drop-down is in A1 and the cell you want to change is in B1 (but you can adjust the references as needed). So in B1 you would wrap the existing formula or figure with IF and put it as the second argument of each IF():

          =IF(A1=Yes”,existing data + 20,IF(A1=”No”,existing data))

          Let me know if you understand that and if any parts confuse you. I’m monitoring these blog comments as well so feel free to ask whatever you need:)
          Kurt LeBlanc

  50. Kash on September 5, 2016 at 9:34 pm

    Thanks for the great tutorial.

    One additional function i need to know.
    First column will be DropDown List and second column just shows the specific date.

    Example.

    We have 5 items names

    A
    B
    C
    D
    E

    And Values as follow

    60
    70
    80
    90
    100

    So I want in the first column A DropDown List which should contains a list of (A,B,C,D,E) and When I select any one of them it should show the related value (A=60) (B=70) etc

    Thank You

    • Kurt LeBlanc on September 6, 2016 at 8:09 am

      Hey Kash,

      The blog can show you how to create the drop-down, but the issue you’re facing is pretty simple:)

      I suggest creating a table of the two columns with the corresponding numbers and letters on the same row. Then your formula back in the second column by your drop-down can lookup the letter in the table you created and return the corresponding number:) I don’t know the specific locations of your data, but this is the logic:

      =VLOOKUP(DROP-DOWN LETTER, TABLE, 2, 0)

      Let me know if that helps!
      Kurt LeBlanc

  51. Rabea Rohde on September 8, 2016 at 7:12 pm

    Good Day!
    I have this scenario I would like your help with please.

    I have a table (on sheet2) with Ingredients, amount bought, its cost and cost per gm. Now I have my Recipe (on sheet 1), where i choose the Ingredients of a drop down list created from “sheet 2” but I don’t want the amount bought or the costs to be shown in my recipe. What i want to happen is this: I add the recipe amount in column 2 and in column 3 I’d like to have the correct cost of gm (which corresponds with the chosen ingredient of the drop down list) multiplied with the amount shown in column 2.

    As I have many Recipe’s to calculate I don’t want to look up the cost in gm manually.

    Can you help me with this, please?

    • Kurt LeBlanc on September 10, 2016 at 7:59 am

      Hi Rabea

      Yes I can help:) What you want to do is use INDEX() function, with MATCH(), to return the amount/gm then multiply that by the amount you’ve selected. This will give you the correct result that will work consistently throughout your report.

      Let me know how this works out for you!
      Kurt LeBlanc

      • Rabea Rohde on September 10, 2016 at 5:57 pm

        Hi Kurt.

        I don’t seem to grasp your idea…. Excel Beginner here :p
        How do I start? Also as I was looking up the INDEX() function it’s a hyperlink. Does this work with drop down Lists?

        Please help. I am totally over my head here!

        Thank you so much!!

        • Kurt LeBlanc on September 11, 2016 at 2:25 pm

          Absolutely! I’m very sorry. INDEX() is a lookup function, but it uses “coordinates” essentially. This is the blog you want:

          https://www.excel-university.com/how-to-return-a-value-left-of-vlookups-lookup-column/

          It would be perfect to find the per gm amount for each ingredient and multiply that by the amount. Hopefully that blog makes things a little easier for you!

          Let me know please
          Kurt LeBlanc

          • Rabea on July 4, 2017 at 8:00 pm

            HI Kurt,
            I had sent you a comment some time ago and am still unable to grasp the whole idea with INDEX() and VLIOOKUP.
            Is there any way that you might be able to help me out with a formula that i could use?
            I don’t know how to lookup a value on sheet 2 and then multiply it with the amount used on sheet 1.
            Also I need to look into how do i LOOKUP if there is a conversion for example from cups to gm, how can I do that?
            Please help.
            Desperate here!
            Thank you, Rabea



  52. Jake on October 1, 2016 at 7:01 am

    have tried to replicate what you have done, to include same names and everything, but when I get the vlookup, there is always an error, not sure what im doing wrong.
    =vlookup([@ItemNum],tbl_items,3,0)
    at this point im lost in what I can do.

    • Kurt LeBlanc on October 4, 2016 at 12:54 pm

      Hey Jake

      Hmm…I’m sorry you’re having trouble. Can you tell me what error you’re returned? It may give me a better clue as to what is going wrong.

      Kurt LeBlanc

  53. Isaac Koomson on October 6, 2016 at 12:44 am

    Please what happens when there is a change in price? what do you do to reflects the new price without distorting the old price.

    • Kurt LeBlanc on October 6, 2016 at 10:58 am

      Hey Isaac

      Fortunately I just did something similar to my own spreadsheet:)

      The way to do it is to have the VLOOKUP() have a range-type lookup where you put the price points, per their effective date, in a table and sort it by the oldest price first. The function will return the first price for any date up to the next price-change date. That may be a little confusing so I’ll give an example:

      =VLOOKUP(report date,price table,nth column, 1(for range))

      Have the record in the final report include a date column that you can use to link to the pricing table and return the price.

      Let me know if you can understand that and if it works out!
      Kurt LeBlanc

  54. Mary Balian on October 13, 2016 at 7:04 am

    hi , i needed to know how to apply these same steps using a table in a separate workbook.

    • Kurt LeBlanc on October 20, 2016 at 5:56 am

      Hey Mary

      Excel has that option built in:)

      When you type your formula, just select the cell in the other workbook and Excel will pull in the file and sheet name before the cell reference. It is pretty amazing how easy Excel makes tasks:)

      Let me know if that works for you,
      Kurt LeBlanc

  55. Frida on October 31, 2016 at 7:03 am

    Good day:
    I need a bit of help since this tutorial is good for retrieving items in different columns. I think I need something similar but retrieving info form different rows within the same column.
    Let me explain. I have two columns with different dates. The first column is the Start_Date column, the second is End_Date; these are columns in my tbl_cicles table.
    In my tbl_pc [price calculation] table I have a cell where I manually input the length in months the contract is for. Beside that cell I used the data Validation to have a drop down list and select any of the dates from my start_date column in the tbl_cicles table. But in the cell beside the start date, I want excel to calculate the end date based on the months I input manually, and grabbing the dates from the end-date column in my tbl_cicles table. So that if I input 3 months, and I select 1-Mar-2017 from my drop down Start Date list, excel would automatically return 30-Jun-2017. Please help and thanks in advance! 😉

    • Frida on October 31, 2016 at 7:06 am

      BTW, I am using Excel 2011 for Mac

      • Kurt LeBlanc on October 31, 2016 at 8:42 am

        Hey Frida

        I do not have Excel for Mac, so I don’t know for sure, but it sounds like a job for EDATE(). Just use that first cell as the reference in the formula.

        Hope that helps:)
        Kurt LeBlanc

        • Frida on October 31, 2016 at 9:44 am

          Hey Kurt,
          Thank you for the super quick reply!
          The EDATE is indeed a very good tool and would be the perfect solution for the application I am trying to solve, were it not because it returns one more day than what I desire.
          To clarify, I have my “Cycles Table”, which has dates based on two cycles, not quite bi-weekly, but exactly from the 1st of each month to the 15th of each month, and from the 16th of each month to the last day of each month. My “Start Date” column contains the dates such as 1-Mar-2017, 16-Mar-2017, 1-Apr-2017, 16-Apr-2017 and so on… on the “End Date” column I have dates such as 15-Mar-2017, 31-Mar-2017, 15-Apr-2017, 30-Apr-2017 respectively. This is because a contract can start either the 1st of each month, or the 16th of each month. However, the contract length is measured by month, not by cycle.
          So if I select from the dropdown menu 16-Mar-2017 in the “Start Date” column of my Price Calculation table, and I want to price out a contract for 2 months, which equal 4 cycles, I want excel to calculate “End Date” grading the information from the “End Date” column I have as part of my “Cycles Table”, because this table contains the exact end date, which is the last day of the month, or the 16th.
          If I use EDATE, and I select 1-Mar-2017 as my start date, and I input 2 months as my contract length, it returns 01-May-2017, not the desired 30-Apr-2017. I wish I could somehow select the cel that contains an equal match to the selected Start Date, and offset it by one column and 4 rows, if that makes any sense.

          • Kurt LeBlanc on October 31, 2016 at 3:47 pm

            Hey Frida

            I understand:) The EOMONTH() function will handle those that start on the first. What you can do is have a simple IF() for your formula where it uses EOMMONTH if the start date is the first and EDATE otherwise like follows:

            =IF(DAY(startdate)=1,EOMONTH(startdate,contractterm),EDATE(startdate,contractterm)-1)

            I subtracted 1 from the EDATE based on what you wanted in your example. You can leave that out if you need.

            Let me know how that works!
            Kurt LeBlanc



  56. chip kelyman on November 2, 2016 at 11:05 am

    Hi Jeff,

    I was wondering if you could help me out with this….i found your tutorial on the web.
    This is a simple form, on the “requisition” sheet I would like for an operator to be able to select a piece of tooling from a dropdown box and have it automatically fill in the price to save him (or her) from having to look it up.
    I am by no means fluent with excel beyond the simple functions…and I seem to be making this far more complicated than I think it really is.
    Again, I’d appreciate any help you could give me.

    Thanks,
    Chip Kelyman

    • Kurt LeBlanc on November 2, 2016 at 11:31 am

      Hey Chip

      Do you have the drop-down set up? If so, what’s your formulas in the rest of the table? The blog walks through the steps, so I’m trying to figure out where you are having trouble.

      Kurt LeBlanc

      • chip kelyman on November 2, 2016 at 11:54 am

        Hi Kurt,
        Yes, the drop down works, it’s adding the VLOOKUP formula that’s hanging me up. When I click on the dropdown box on the first sheet It’s not bringing the individual price from the second column on the “End Mill List” sheet.
        Thanks again!

        • Kurt LeBlanc on November 3, 2016 at 5:55 am

          Alright, can you give me your formula for the VLOOKUP()?

          Thanks,
          Kurt LeBlanc

  57. Frida on November 2, 2016 at 1:50 pm

    Kurt:

    Worked like a charm! You rock! Thanks!

    • Kurt LeBlanc on November 3, 2016 at 5:53 am

      So glad it worked!

      Kurt LeBlanc

  58. chip kelyman on November 3, 2016 at 6:40 am

    Sure thing Kurt, the VLOOKUP formula looks as such:

    =VLOOKUP(“@ItemNum”,tbl_items,2,0)

    The message I get is:

    “This value doesn’t match the data validation restrictions defined for this cell”

    • Kurt LeBlanc on November 3, 2016 at 7:22 am

      Hmmm…which cell is the formula in? And it’s just a simple mistake in your formula otherwise: the cell reference is in quotes, so Excel reads that as the actual text. Remove the quotes and make sure the formula is in the cell you want the result in. Cells have 2 values: stored and displayed values. Every cell can store a formula and display the results. I think you might be thinking you need the formula somewhere else.

      Let me know if that helps,
      Kurt LeBlanc

  59. chip kelyman on November 4, 2016 at 3:47 am

    That did it, thank you for your help Kurt! I knew i was making it more difficult than it needed to be.

    • Kurt LeBlanc on November 4, 2016 at 5:26 am

      Very good Chip!

  60. Chris on November 11, 2016 at 9:49 am

    Hello Kurt,

    I have created a data entry page in Excel that populates an Invoice/Purchase Agreement form as well as other necessary forms. The invoice has columns titled “Quantity”, “Item”, “Description” and “Price”. The Item column contains a drop down box in each row that is attached to an Item Table. I would like to do exactly what your demonstration above shows, but the formula does not work. This is the formula I entered.

    =IFERROR(VLOOKUP([@Item],tbl_items,3,0),””)

    The error message I get says “The name you entered is not valid”. When I click OK, Excel returns to the formula with [@Item] highlighted. I have also tried the same formula except with the “@” in front of “Item” deleted. What am I doing wrong? Any help would be greatly appreciated.

    Sincerely,
    Chris

    • Kurt LeBlanc on November 14, 2016 at 4:54 pm

      Hey Chris

      Try this with “Item”: VLOOKUP([@[Item]],,…

      Instead of typing a formula, I just clicked the cell I wanted to reference, and that’s what Excel entered automatically. Everything else looks good, so hopefully that’s it.

      Let me know,
      Kurt LeBlanc

      • Chris on November 19, 2016 at 8:31 am

        Hello Again Kurt,

        Tried the suggestion from your email, but it didn’t work. Excel gave me the same name error message. Copied and pasted the exact formula from your email, but that didn’t work, same error message. I’m missing a step here somewhere. I have 4 tables connected to drop down boxes, 2 of them have an Item column. In the formula, it doesn’t seem that I’ve told Excel which table or which column of data to return. What have I missed? Did I set up the tables incorrectly? They work correctly with the drop down boxes. Thank you for your help.

        Chris

        • Kurt LeBlanc on January 4, 2017 at 10:31 am

          Hey Chris

          I’m sorry we’re having so much trouble…Try typing your original formula but use [@[Item]] instead of [@Item]. That’s the only thing I can think of right now…

          Let me know how that works please,
          Kurt LeBlanc

  61. Chris on November 11, 2016 at 2:36 pm

    By the way, I believe I figured out how to correct the name issue. I replaced brackets with quotation marks and Excel accepted the formula. However, with the formula shown below, Excel apparently doesn’t know where to look for the item. It returns nothing.

    =IFERROR(VLOOKUP([@Item],tbl_items,3,0),””)

    When I remove the IFERROR, it returns “#NAME”. How do I tell Excel where to look up the items?

    Thanks,
    Chris

    • Kurt LeBlanc on November 15, 2016 at 8:19 am

      Hey Chris

      Did you try the solution I sent you earlier? Let me know how that works out.

      Also, quotation marks signify an exact text string, so only use them when appropriate. Most references need to be cell addresses instead in my experience.

      Kurt LeBlanc

  62. Bradley Davidson on November 11, 2016 at 5:40 pm

    This is a very useful tutorial but I have a slight expansion on this that I need to make.
    We have a list of items and I can retrieve it multiple ways, but once I select the item I need to provide 2 sizes that can be selected to calculate the price.
    For example:

    Item 1 – 1; 2′; $1.00
    Item 1 – 2; 2′; $2.00
    Item 1 – 1; 3′; $2.00
    Item 1 – 2; 3′; $6.00
    Item 1 – 3; 3′; $9.00

    I have a sheet containing every item individually and a grid that lists the items based on the sizes select.

    So once they pick the item from the drop down list, I want the 2 size columns to list the available size choices.

    How can I provide this secondary lookup to list my seizes?

  63. Tracie on December 19, 2016 at 5:32 pm

    Hello,
    Hoping you can help me out, can’t quite seem to figure this out!

    I have created a Table called “SpaceTypes” on the second Sheet of a workbook, that has 3 columns. I named the second column “SpaceTypeNames” and it includes the items that I want in the drop down list.

    On the first Sheet of a workbook, I am able to get cell A8 to have the drop-down with those names. In cell C8 of that first Sheet, I’ve typed =VLOOKUP(A8, SpaceTypes,3, 0) but keep getting a #N/A.

    Not sure what I am doing wrong??? Thanks in advance!!

    • Kurt LeBlanc on January 4, 2017 at 9:00 am

      Hey Tracie

      The #N/A error is returned because the formula can’t find something. VLOOKUP looks at the left-most column in a range, so when you use the table as the reference, the formula looks in the first column of the table to find the lookup value. That’s why it can’t find the value you are searching for:)

      You can handle this two ways:

      1. Move the column to the first position

      or

      2. Use the INDEX/MATCH combo for your formula instead. This blog explains this process really well: https://www.excel-university.com/how-to-return-a-value-left-of-vlookups-lookup-column/

      Let me know if you need any further help!
      Kurt LeBlanc

      • Tracie on January 5, 2017 at 12:38 pm

        Moving the column to the first position worked, and will check out the INDEX/MATCH method just for my own info. Thank you so much!

  64. Mohammad Ahmed on February 21, 2017 at 1:24 am

    Hi, Is t possible for you to provide me the same content with formulas in it?

    • Jeff Lenning on February 21, 2017 at 4:34 am

      The sample file contains the formulas…hope it helps!

      • Mohammad Ahmed on February 21, 2017 at 5:07 am

        Hi,
        I have tried to do as you have mentioned but couldn’t able to do it. If you provide me the excel sheet that would be a great help.

        • Jeff Lenning on February 21, 2017 at 5:15 am

          You can download the Excel workbook by clicking the sample file link in the Additional Resources section.

          • Mohammad Ahmed on February 21, 2017 at 6:04 am

            Thank you so much. It helped.



          • Jeff Lenning on February 21, 2017 at 6:08 am

            Welcome 🙂



  65. Robert Allen on April 18, 2017 at 12:20 pm

    Jeff:
    I thought my autopopulate problem was solved with the Item Num table you used. In my case all values in my table are text. I defined my first column ads type_items for the data for my drop down list. Based on user selection of an item from type_items I want to populate a text field from colum 2 of the full table tbl_typefill. I entered the following formula =vlookup([@type_items],tbl_typefill,2,0) and get syntax error messages even with variations such as =vlookup([type_items],tbl_typefill,2,0) and replacing 0 with FALSE.

    Is the problem the fact that my data is text, not numerical?? Advide is most appreciated.
    Bob

    • Jeff Lenning on April 21, 2017 at 4:50 am

      The first argument of VLOOKUP is a single cell reference, the lookup value. So instead of using a column reference, try using the cell reference of your primary drop down. Hope it helps!

  66. Shumailah on April 25, 2017 at 10:58 pm

    Hi Jeff!!

    Based on the selection in my drop-down list, I want to return a value by looking up in one of my multiple sheets.

    For example, my first drop down list has two options Fruits and Vegetables. My second drop down list then gives the following options for Fruits: Red Fruit and Green Fruit; for vegetables: Red Vegetables and Green Vegetables. and maybe a third drop down dependent list where Red fruit can contain apples, strawberries etc.

    Now I have different tables/sheets for Red Fruit, Green fruit, Red Vegetables and Green Vegetables which describe the mass, date of harvest, etc for each fruit.

    I want to return for example the Mass in my main sheet based on the drop down lists. How can I do that?
    I believe the problem is slightly complex.

    Thanks a lot in advance!

  67. Lloyd on May 5, 2017 at 4:55 am

    Hi Jeff

    How can I best do this for a table that needs to look down a column as well as across rows?

    I’m trying to design a tool using v-lookup that will help my agents quickly identify the suitability AND cost of a product, based on the age of the customer. Currently my date looks a bit like this:

    Age Product 1 Product 2 Product 3
    0-39 5000 4600 2500
    40 – 59 2500 2000 1500
    60 – 70 800 500 N/A

    I’d like the tool to be able to call the prices of the different products based on the age selected – I hope this is possible and this whole thing makes sense!

    Love this site – learnt so much so far!

    Lloyd

  68. Jennifer on June 27, 2017 at 10:58 am

    Hi, I use VLOOKUP All the time, but what I can’t figure out is how to make the cell have the value, but to show the value and not the formula when you click in the cell. Is this possible?

  69. Samuel Arthur on July 10, 2017 at 1:08 pm

    Hi Jeff,

    I have a question regarding drop down list and auto update to a cell after every new entry. Please help me. My excel sheet is as below:

    Column A= Empty
    Column B= Date
    Column C= Store Name
    Column D= Cost in USD
    Column E= Purpose (This is where my drop down list is chosen from, the Validation Criteria list is obtained from Column S List (List has Rent, Grocery (Cell=S12) equivalent value is T12, Phone, Medical, Utility etc as options – something similar to a monthly budget)

    So, I would like the Grocery Cell value (T12 in column T) to fill automatically and get the cost updated/added up (up to date cost) every time I add a grocery charge. Such as below:

    Data–Store Name–Cost–Purpose [“Column T” for “Cell T12” value to be]
    07/01/2017–Walmart–$26.50-Grocery After 07/01/2017 Entry -> T12=$26.50
    07/04/2017–Sams Club–$16.50-Grocery After 07/04/2017 Entry -> T12=$43.00
    07/06/2017–Walmart–$26.25-Grocery After 07/06/2017 Entry -> T12=$69.25
    07/10/2017–Costco–$6.50-Grocery After 07/10/2017 Entry -> T12=$75.75
    07/11/2017–Walmart–$10.50-Grocery After 07/11/2017 Entry -> T12=$86.25
    And so on…….

    Thank you,
    Sam Arthur

  70. Ron Shane on August 9, 2017 at 11:23 am

    I have looked and looked and don’t see anywhere how to create an input table.
    I have seen on line how to create a table… but not an input table.
    Simply creating a table won’t allow me to copy the drop down list and the Vlookup rules to lower rows.
    PLEASE help me. I am so frustrated.
    I have my companies on sheet 2 called Rules
    I want sheet one to have a input table called Quotes.
    I want to be able to do a drop down from Rules under the Company column in Quotes. Then I will manually input prices on that row.
    I also want to drop to lower rows, use the drop down list to pull in different Company from Rules and it populate the specifics for that company so the Quotes table ends up having several companies with their specific info populated from the Vlookup so I can compare quotes for various companies.

    Thank you

  71. Harpreet P on August 14, 2017 at 10:28 am

    Hello Jeff,

    I am trying to do something a little more complex. For example I have a column that states width, one for height, one for depth, and then also one for type of product. I want to be able to select all the correct information and based on that I want he price to populate. eg.
    Type (colour): white (this could change to other styles and colours)
    Width: 10 (this could change to another number)
    Height: 3 (this could change to another number)
    Depth: 10.26 (this could change to another number)
    Based on the above, I would like the price (with a combination of all selections) to generate in another selected cell. Is this possible?

    Please advise.
    Thank you,
    Harpreet P

  72. Darren on May 24, 2018 at 12:58 pm

    Hi Jeff,
    I have a drop down list in cell D12 named Function. I have 8 options that can be chosen from the drop down. (example 1000,2100, 2210…). In Cell E12 named Object, would like to create a drop down list that depends on what is selected from Function D12. Function 1000 has 19 selections, 2100 has 3 selections, 2210 has 19 selections.
    I don’t know which “EXCEL FUNCTION OR FORMULA” to use to make this happen. If D12 drop down is used, would like for E12 selections to be determined by what is selected in D12. Almost a drop down list inside a drop down list. Familiar with VLookup, but it only allows for one column to be selected instead of a drop down list. I look forward to your response.

    • Kurt LeBlanc on July 2, 2018 at 11:14 am

      Hi Darren!

      You are right in that VLOOKUP can’t return a list like that. What you’ll have to do is create a “dependent” drop-down, one that is created dynamically after you make the first selection. The blog at the link below will walk you through a few different ways of creating such drop-down lists:
      https://www.excel-university.com/create-depdendent-drop-downs-conditional-data-validation/

      Let me know if that helps!

      Thank you,
      Kurt

  73. Alan on September 8, 2019 at 10:50 am

    Hi Darren

    How would I be able to link the drop downs to a particual client?
    So if I highlighted a client name, the only items in the drop down would only those belonging to that client.

    Hope that makes sense.

    Hope you can help

    Cheers

    Al

  74. jim on April 23, 2020 at 9:20 am

    I have a worksheet called “States” that looks like this:

    States (D1) Color (E1) Temperature (F1) Miles (G1)
    Arizona (D2) Yellow (E2) Hot (F2) 50 (G2)
    California (D3) Green (E3) Medium (F3) 100 (G3)
    Texas (D4) Blue (E4) Cold (F4) 200 (G4)

    I have another worksheet called “Inputs” that has a drop down menu list in cell B9 that refers to list of states from Column D on worksheet States above.

    I have another worksheet called “Outputs” where I want the corresponding Color, Temperature, and Miles to show when the state is selected on the Input worksheet.

    For example, I choose “Arizona” from drop down menu cell B9 on Input Worksheet, Output Worksheet should show

    Yellow (cell A39))
    Hot (cell A40 )
    50 (cell A41)

    What formula do I place in cell A39 Worksheet “Outputs” in order to look return “Yellow”?

  75. Rajaram on July 11, 2020 at 2:15 am

    I want to use custom list and string on certain conditions for the same cell. Is that possible?
    e.g. for one cell(C10), I need to have data validation.
    in A1 if condition-1 then at C10 , i need drop down.
    if in A1, condition-2 then at C10, i need fixed value “Ok”.
    is this possible?

  76. ziad on January 18, 2021 at 7:06 am

    Hello,

    I have a problem with one drop down list on another sheet, where i have 3 items in it. I want each item to extract data from my data sheet.

    the items in the drop down list are salespersons (have 3).
    the data is as follows:

    table consists of around 20 columns, but the columns relevant are: names of clients – salesperson 1 commissions, salesperson 2 commissions, salesperson 3 commissions (all in $ numbers)

    I want to retrieve the names of clients of each salesperson. can it be done with one drop down: salespersons! and get the clients names related to that salesperson with the amount of commission from each client next to it?

    thanks for your help

  77. Gino Larsen-Giacalone on January 25, 2021 at 3:54 pm

    My Project:
    A drop-down(?) list that populates several cells – BUT:
    1. Source list is over 100 names long – so only want to show set with the same first letter in list.
    2. Source data changes daily – but entries from previous days (on previous lines in destination sheet) should not change
    3. Source data is in a separate sheet

    Maybe an entry completion pop-up is what is needed? I’m thinking that a VBA function that populates the popup is the direction to go.

  78. Joel Bravo on January 26, 2021 at 2:53 pm

    Hi,

    I was needing to find answer to my question on Data Validation Dynamic Drop-Down List+VLOOKUP. I hope you guys can help me achieve what I want to do.

    So I’ll start off by describing what I would like to do. I have different sheets I would like to look up data on. I made normal data validation drop down lists for my PHASE, PURPOSE, & SPECs. I added a bit of a twist to the CATEGORY to where is only shows the amount of rows under the certain spec and there not be empty slots. All these work within the first two sheets which is fine.

    Now for the problem or solution I need to learn;

    I need it to work in the following manner; you select your SPEC, after that the CATEGORY column will display the categories under that SPEC, then your CHOICES column will display the choices under that SPEC+CATEGORY. So every SPEC has it own sheet within the workbook.

    I don’t know how to make it work to where the CHOICES column displays whatever is in its corresponding sheet. BTW i only added about 15 SPECs its work in progress in case I had to go a different route.

    I would greatly appreciate any help on this and if I what I am wanting to do is not Dynamic Data Validation Drop Down List + VLOOKUP let me know or if there is an easier way I am all ears.

    Thanks,

    Bravo

  79. Prabhakar Sastri on April 22, 2021 at 5:41 am

    I have selected an item from the drop down box. How do I paste this value in another column. For example if I select a number “seven” from a drop down, how can I write “seven” in the next column ?

  80. Gayle on September 12, 2023 at 1:05 pm

    Hello! After reading this thread I think this formula is exactly what I need, but I am running into a #SPILL error and hope you can help. Here is what I have:

    In a separate DATA tab on the same sheet, I have a table with the columns Item name, description, and unit price. I’ve added the drop down list to the main tab and then used this formula =IFERROR(VLOOKUP(Item_No,LAS_Price_List, 2, 0), “”) in the column to the right, intending to populate column C with the information once the item is selected from the drop down in column B. The #SPILL error is happening, I think because this formula seems to be basically just indicating to copy and paste the entire column, rather then paste one selected row as indicated by the first drop down.

    What am I missing?

Leave a Comment