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.

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.

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

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.

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.

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

## Additional Resources

- Sample file: ItemLookup
- Other VLOOKUP posts: VLOOKUP
- Other Table posts: Table
- Other Data Validation posts: Data Validation

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!

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

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.

Thanks for sharing!

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.

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

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

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

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

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

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

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

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.

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

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

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!

How exactly would IFERROR be used here? Thanks!

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

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

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

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

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.

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

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!

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

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

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

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

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

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

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

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.

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

🙁 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

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

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

William…sounds like you want to create dependent drop-downs, where the choices in the secondary drop-down depend on the selection made in the primary drop-down. Here are two of my blog posts that walk through the details:

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

Thanks,

Jeff

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/

Ben,

Ah…yes…thanks for the assist!

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

Thanks

Jeff

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

Jesse,

Thanks for your question…I decided to do a post about that topic:

http://www.excel-university.com/two-dimensional-vlookup

Thanks

Jeff

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?

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

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

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

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?

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

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

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

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.

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!

Hey Debbie,

This sounds like a job for the SUMIFS function:) It can add together only rows that meet your criteria. Mr. Jeff wrote a great blog on this:

http://www.excel-university.com/multiple-condition-summing-in-excel-with-sumifs/

Let me know if that works out for you,

Kurt LeBlanc

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

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

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

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

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.

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

Many thanks Jeff. Greatly appreciated.

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

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

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.

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

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!

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!

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

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

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

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

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!

Hi Ross! To accomplish the two related drop downs, you could use the techniques discussed here: http://www.excel-university.com/create-depdendent-drop-downs-conditional-data-validation/

And to return a price based on the two conditions, you could use the SUMIFS function discussed here : http://www.excel-university.com/multiple-condition-summing-in-excel-with-sumifs/

Hope it helps!

Thanks

Jeff

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

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

Thanks Jeff….works perfectly…!!

🙂

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!

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

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

Hey Matt,

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

Kurt LeBlanc

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!

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

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

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

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.

Hi! Sounds like you are looking to create dependent drop-downs based on shift and team lead. I walk through several ways to set that up in this post:

http://www.excel-university.com/create-depdendent-drop-downs-conditional-data-validation/

Hope it helps!

Thanks

Jeff

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?

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

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.

Hey Patrick

I believe you are looking for dependent drop-downs and hopefully this can help:)

http://www.excel-university.com/create-depdendent-drop-downs-conditional-data-validation/

Let me know if this works!

Kurt LeBlanc

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.

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

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

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.

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

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

Patrick,

Thanks for your question. Since it has broad appeal, I’ve gone ahead and answered it by creating the following post:

http://www.excel-university.com/unpivot-excel-data/

Hope it helps!

Thanks

Jeff

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.

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

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

Hey Terry,

Everything is working on my end. I’m not sure what the problem is…I pasted the link below in case its just being funny:

http://www.excel-university.com/wp-content/uploads/ItemLookup.xlsx

Let me know if this helps!

Kurt LeBlanc

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,

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

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?

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

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!

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

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?

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

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.

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

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.

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

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

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?

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

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?

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

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.

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

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

Hey Reed,

This certainly sounds like a task Excel is up for:) It sounds like you want dependent drop-down lists. Mr. Jeff wrote a great blog on this! I hope you find it useful!

http://www.excel-university.com/create-depdendent-drop-downs-conditional-data-validation/

Let me know if you need anymore help,

Kurt LeBlanc

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.

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

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

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

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

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

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?

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

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

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

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.

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

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

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

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

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

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

BTW, I am using Excel 2011 for Mac

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

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.

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

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

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

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!

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

Thanks,

Kurt LeBlanc

Kurt:

Worked like a charm! You rock! Thanks!

So glad it worked!

Kurt LeBlanc

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”

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

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

Very good Chip!

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

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

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

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

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

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

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?

Hey Bradley

Check out this article o create your second drop-down: http://www.excel-university.com/create-depdendent-drop-downs-conditional-data-validation/

It sounds perfect for you:)

Let me know if that helps you and I’m happy to provide additional help,

Kurt LeBlanc

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

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

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!