Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return specific values based on multiple crireria
I have a datasheet that contains numeric values for a large number of
countries (rows) in multiple years (columns). I want to insert two drop down lists into two cells of the worksheet - one containing a list of countries and the other a list of years. When a country and year is selected from each list, I want to have a function that will return a specific value from the datasheet (the value for the selected country in the selected year) into a third cell. I need it to work so that each time I change the combination of country and year using the two drop down lists, the value returned in the third cell changes to the relevant value in the datasheet. Does anyone know how I can achieve this, please. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return specific values based on multiple crireria
I mocked up some data like this: Country name (indicated by your
dropdown) in A2, and a Year (also indicated by your dropdown) in B2. In G1 I have a label "Country", then in H1:J1 I have some year numbers. In H2:J4 I have some data. The formula I used is: =VLOOKUP(A2,$G$2:$J$4,MATCH(B2,$H$1:$J$1,0)+1,0) The MATCH is nested in the VLOOKUP as the column number argument. The +1 augments the integer returned by MATCH to account for the "Country" column. Dave O Eschew obfuscation |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return specific values based on multiple crireria
Dear Dave
Your solution worked perfectly - thank you so much! All the best, Ian "Dave O" wrote: I mocked up some data like this: Country name (indicated by your dropdown) in A2, and a Year (also indicated by your dropdown) in B2. In G1 I have a label "Country", then in H1:J1 I have some year numbers. In H2:J4 I have some data. The formula I used is: =VLOOKUP(A2,$G$2:$J$4,MATCH(B2,$H$1:$J$1,0)+1,0) The MATCH is nested in the VLOOKUP as the column number argument. The +1 augments the integer returned by MATCH to account for the "Country" column. Dave O Eschew obfuscation |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return mulitple values based on the contents of another cel | Excel Discussion (Misc queries) | |||
search multiple sheets for specific date, return data in cell to r | Excel Discussion (Misc queries) | |||
Search multiple values to return single values | Excel Worksheet Functions | |||
Return a cell value based on specific combinations of cells in an array | Excel Worksheet Functions | |||
Adding multiple cells, return specific values | Excel Worksheet Functions |