Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

excel get value according to a dropdown list

Posted on 2011-10-06
10
Medium Priority
?
284 Views
Last Modified: 2012-08-13
I have asked a question similar to this before and got a pretty good yet confusing answer, however would not work in this exact case very well.
I am attaching an example of what I am looking for hoping you could make the change that I need.

I have a list of values that I set to range list 1. Beside list 1 I have another list of values list 2 that correspond to each adjacent value in list 1.  Then i created a drop down list with those values from list 1. What i want to do is when someone selects a value from list 1 it displays the value from list 2 in another cell.

confused yet? If you look at the spreadsheet it should help. So if "granted" is selected from the dropdown list 1 then in C2 it will populate "this is what I want displayed if granted is selected"

please help this is very important. thank you.  example.xlsx
0
Comment
Question by:jacobJL
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 9

Assisted Solution

by:chwong67
chwong67 earned 200 total points
ID: 36928074
use formula below for C2:
=INDEX(G4:G9,MATCH(B2,list1,0),1)
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 800 total points
ID: 36928160
You could also use VLOOKUP, name F4:G9 range (both columns) table and then try this formula for C2

=VLOOKUP(B2,Table,2,0)

regards, barry
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 1000 total points
ID: 36928164
Since "granted" is in the column to the left of "this is what I want displayed if granted is selected" you could use VLOOKUP:
=VLOOKUP(B2,F4:G9,2,FALSE)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jacobJL
ID: 36928339
I don't think you guys realize how awesome you are! byundt i used your solution just because it was closest to what i was trying. Gonna keep this open for till tomorrow just to make sure I don't have anymore questions. Thank you everyone.
0
 

Author Comment

by:jacobJL
ID: 36931011
Ok so I do have another question. I want to add 2 more columns right next to list 2 . These columns may or may not have values in them. So in the example I provided, if granted was select, then the corresponding value from list2 will be displayed in c2 but also if there is a value in either list 3 or 4 then I also want to show them in another cell like c5 and c6. So list 3 will be displayed in c5 and list 4 in c6. Is this possible? Thanks again for your awesomeness.
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 800 total points
ID: 36931086
you can use VLOOKUP again as per mine and byundt's suggestions. If you extend te lookup range to include more columns and use 3 and 4 for the column index that will do what you want, i.e. in C5

=VLOOKUP(B2,F4:I9,3,FALSE)

and in C6

=VLOOKUP(B2,F4:I9,4,FALSE)

The lookup value (B2 here) is always matched against the first column of the lookup range (F4:F9 here) and then the result is taken from the column specified by the "column index" so 3 gives you the value from column 3 of the range (H4:H9) and 4 from the 4th column (I4:I9) - see attached

regards, barry
27384699.xlsx
0
 
LVL 81

Accepted Solution

by:
byundt earned 1000 total points
ID: 36931088
The lookup table can be expanded as much to the right as you like:
=VLOOKUP(B2,F4:I9,2,FALSE)          formula for C2
=VLOOKUP(B2,F4:I9,3,FALSE) & ""   formula for C5
=VLOOKUP(B2,F4:I9,4,FALSE) & ""   formula for C6

The VLOOKUP would normally return a 0 if the column for list3 or list4 were blank. By concatenating an empty string, you get a blank instead.
0
 

Author Comment

by:jacobJL
ID: 36931332
OHHHHHHHhhhh I get it now. Didn't realize what the 3rd parameter (2, 3, and 4 ) was. Byundt thank you very much. Thanks to every one else as well, the response time was amazing.
0
 

Author Comment

by:jacobJL
ID: 36931354
I'm sorry barryhoudini I didn't even see your explanation about the columns. Thank you.
0
 

Author Closing Comment

by:jacobJL
ID: 36931381
Love this site. You all rock!
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question