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

This article was written by Jeff Lenning

170 comments:

  1. Alison
    Reply

    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!

    1. jefflenning Post author
      Reply

      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

    2. megan
      Reply

      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.

      1. jefflenning Post author
        Reply

        Thanks for sharing!

    3. SV
      Reply

      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.)

  2. Dawn
    Reply

    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.

    1. jefflenning Post author
      Reply

      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:

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

      Thanks
      Jeff

  3. Greg Perry
    Reply

    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

    1. jefflenning Post author
      Reply

      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:
      http://www.excel-university.com/how-to-return-a-value-left-of-vlookups-lookup-column/

      Hope this helps!

      Thanks
      Jeff

  4. frank Sloan
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    “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!

    1. jefflenning Post author
      Reply

      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

      1. Lee
        Reply

        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.

        1. jefflenning Post author
          Reply

          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

          1. Lee

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

          2. jefflenning Post author

            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!

          3. Lee

            How exactly would IFERROR be used here? Thanks!

          4. jefflenning Post author

            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
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    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

    1. Cameron
      Reply

      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.

      1. jefflenning Post author
        Reply

        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
    Reply

    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!

    1. jefflenning Post author
      Reply

      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
    Reply

    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

    1. jefflenning Post author
      Reply

      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

      1. Adam
        Reply

        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

        1. Kurt LeBlanc
          Reply

          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
    Reply

    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

    1. jefflenning Post author
      Reply

      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

      1. Robert
        Reply

        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.

        1. jefflenning Post author
          Reply

          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

          1. Robert

            🙁 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

          2. Kurt LeBlanc

            Yes, my email is kurleb10@gmail.com and please explain your question again.

  11. William
    Reply

    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
    Reply

    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:
    http://www.excel-university.com/how-to-return-a-value-left-of-vlookups-lookup-column/

    1. jefflenning Post author
      Reply

      Ben,

      Ah…yes…thanks for the assist!

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

      Thanks
      Jeff

  13. Jesse
    Reply

    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
    Reply

    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?

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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] ?

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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?

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    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
    Reply

    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
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    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.

    1. jefflenning Post author
      Reply

      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

      1. Kevin
        Reply

        Many thanks Jeff. Greatly appreciated.

  23. Pauline Chua
    Reply

    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!!

    1. jefflenning Post author
      Reply

      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
    Reply

    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.

    1. jefflenning Post author
      Reply

      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
    Reply

    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!

    1. Alison
      Reply

      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!

      1. jefflenning Post author
        Reply

        Alison,
        Oh yes, it sounds like you want to create a drop-down that has a list of choices (vendor names) depending on the selection of another drop-down (vendor type). There are a few different ways to set this up in Excel, and I’ve written a couple of posts that walk through them:
        http://www.excel-university.com/slicers-as-an-alternative-to-conditional-drop-downs/
        http://www.excel-university.com/create-depdendent-drop-downs-conditional-data-validation/
        Hope they help…thanks!
        Thanks
        Jeff

    2. jefflenning Post author
      Reply

      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
    Reply

    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

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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
    Reply

    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

    1. jefflenning Post author
      Reply

      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

      1. David Watkins
        Reply

        Thanks Jeff….works perfectly…!!

        1. jefflenning Post author
          Reply

          🙂

  29. Tess Fullard
    Reply

    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!

    1. jefflenning Post author
      Reply

      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
    Reply

    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

    1. Kurt LeBlanc
      Reply

      Hey Matt,

      I’ll be happy to take a look at it for you:) My email is kurleb10@gmail.com

      Kurt LeBlanc

  31. Cindy
    Reply

    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!

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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

    1. Kurt LeBlanc
      Reply

      Hey Jenny:)

      The error means you are referring to the whole table instead of one column for the list. If you can email me your file, I can help you more thoroughly…kurleb10@gmail.com

      I’m happy to help!
      Kurt LeBlanc

  33. Omokayode
    Reply

    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
    Reply

    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?

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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.

    1. Beau Crandall
      Reply

      Patrick,
      It sounds to me like you are trying to do exactly what I came here to find.
      When I have a full, error free solution I will reply. I should have it done soon.

  36. Rob
    Reply

    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

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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.

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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
    Reply

    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.

    1. Kurt LeBlanc
      Reply

      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
    Reply

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

  41. Georgina Lawry
    Reply

    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,

    1. Kurt LeBlanc
      Reply

      Hey Georgina

      You can absolutely email me at kurleb10@gmail.com so I can try to help you:) I’ll ask Mr. Jeff if its beyond me.

      I hope I can help!
      Kurt LeBlanc

  42. Shihab
    Reply

    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?

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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!

    1. jefflenning Post author
      Reply

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

  44. Zach
    Reply

    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?

    1. Kurt LeBlanc
      Reply

      Hi Zach,

      I’m sorry you’ve been having so much trouble…It sounds like the issue the blog addresses, but I may be missing something. Could you send your file to kurleb10@gmail.com so I can better help you?

      I’m always happy to assist:)
      Kurt LeBlanc

  45. Wesley
    Reply

    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.

    1. Kurt LeBlanc
      Reply

      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

      1. Wesley
        Reply

        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.

        1. Kurt LeBlanc
          Reply

          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

        2. jefflenning Post author
          Reply

          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

          1. Weslley

            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?

          2. Kurt LeBlanc

            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
    Reply

    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?

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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.

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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
    Reply

    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.

    1. Kurt LeBlanc
      Reply

      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

      1. Joe
        Reply

        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

        1. Kurt LeBlanc
          Reply

          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
    Reply

    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

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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?

    1. Kurt LeBlanc
      Reply

      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

      1. Rabea Rohde
        Reply

        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!!

        1. Kurt LeBlanc
          Reply

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

          http://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

  52. Jake
    Reply

    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.

    1. Kurt LeBlanc
      Reply

      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
    Reply

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

    1. Kurt LeBlanc
      Reply

      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
    Reply

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

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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! 😉

    1. Frida
      Reply

      BTW, I am using Excel 2011 for Mac

      1. Kurt LeBlanc
        Reply

        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

        1. Frida
          Reply

          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.

          1. Kurt LeBlanc

            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
    Reply

    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

    1. Kurt LeBlanc
      Reply

      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

      1. chip kelyman
        Reply

        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!

        1. Kurt LeBlanc
          Reply

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

          Thanks,
          Kurt LeBlanc

  57. Frida
    Reply

    Kurt:

    Worked like a charm! You rock! Thanks!

    1. Kurt LeBlanc
      Reply

      So glad it worked!

      Kurt LeBlanc

  58. chip kelyman
    Reply

    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”

    1. Kurt LeBlanc
      Reply

      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
    Reply

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

    1. Kurt LeBlanc
      Reply

      Very good Chip!

  60. Chris
    Reply

    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

    1. Kurt LeBlanc
      Reply

      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

      1. Chris
        Reply

        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

        1. Kurt LeBlanc
          Reply

          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
    Reply

    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

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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
    Reply

    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!!

    1. Kurt LeBlanc
      Reply

      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: http://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

      1. Tracie
        Reply

        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
    Reply

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

    1. Jeff Lenning Post author
      Reply

      The sample file contains the formulas…hope it helps!

      1. Mohammad Ahmed
        Reply

        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.

        1. Jeff Lenning Post author
          Reply

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

          1. Mohammad Ahmed

            Thank you so much. It helped.

          2. Jeff Lenning Post author

            Welcome 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.