Return specific values based on multiple crireria

  • Thread starter Thread starter Ian S
  • Start date Start date
I

Ian S

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.
 
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
 
Back
Top