0

I am using Excel and have a table. I want to make the information from the table auto fill - by this I mean I have a selection of titles and all these titles have a value, dollar value etc.

When I select the title, is it possible to get the other information to auto fill? Is there a formula or a way to do this?

  • You may need to provide some screenshot or more information of what kind of view you are hoping to see.... a before and after so we know what you wanted. For now, it is very unclear what you wanted. – Darius Nov 14 '16 at 3:40
  • The idea is: When a code is selected from the dropdown list in column F, the corresponding dollar amount and code description automatically appear in columns G & H. I have added a screen shot to the main question. – LMS Nov 14 '16 at 4:13
  • Where's the image? – mcalex Nov 14 '16 at 4:30
  • I made the link bigger – LMS Nov 14 '16 at 4:35
  • I only have partial answer... to fill in the content of G and H - you can achieve this using =VLOOKUP function. The code would be (on G12) something like =VLOOKUP(F12, A1:C100,2). It basically means look up code in F12 to match the table of A1:C100, and then if a match found, return content in column index 2 (which is Column B) - you can use the same code for H, and return index 3. I don't know how to make the drop down menu... so if someone can expand on this? – Darius Nov 14 '16 at 5:07
2

You need a basic VLOOKUP formula. In your example image, in cell G11 enter the formula =VLOOKUP(F11,A:C,2,FALSE). In cell H11 you would need =VLOOKUP(F11,A:C,3,FALSE)

F11 contains your lookup value, A:C is the cell range for your table and the value following that is the col_index value (the column of the cell range) to be returned. (If A:C was B:D then col_index of 2 would return the value in column C).

The last bit which is the range_lookup value controls whether value needs to match exactly or not.

Set range_lookup to FALSE to require an exact match and TRUE to allow a non-exact match.

If range_lookup is TRUE (the default setting which is also used if range_lookup is not set) you need to make sure that lookup values in the first row of the table are sorted in ascending order. Otherwise, VLOOKUP may return an incorrect or unexpected value.

If range_lookup is FALSE (require exact match), values in the first column of table do not need to be sorted.

You have already got your drop down boxes sorted but for completeness, to get drop down lists in column F,

  1. Select column F. (You can position the drop-down list in most any cell or even multiple cells.)
  2. Choose Validation from the Data menu.
  3. Choose List from the Allow option's drop-down list.
  4. Click the Source control and simply enter the reference =$A:$A.
  5. Make sure the In-Cell Dropdown option is checked.
  6. Click OK.
  • A good answer. You could also put it in a IF to not fill the G and H column with "N/A" or whatever it returns when nothing is chosen. – Christofer Weber Nov 14 '16 at 6:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.