Index and Match Question.

[Tarun Kanti Dey]

I have a table of data.


TABLE B May-12 Jun-12 Jul-12

AT - Austria 260 270 280

BE - Belgium 350 340 340

DK - Denmark 350 340 300

EE - Estonia 18 24 24

FI - Finland 180 180 180

FR - France 2,100 2,100 2,130

DE - Germany 3,425 3,365 3,465


And I am trying to use Index and Match to derive the following figures:


TABLE A May-12 Jun-12 Jul-12

WESTERN EU

AT - Austria

DE - Germany

DK - Denmark

FI - Finland

IS - Iceland

NO - Norway


TABLE A May-12 Jun-12 Jul-12

WESTERN EU

AT - Austria 260 =INDEX(H8:K14,MATCH(B9,$B$9:$B$14,0),2)

DE - Germany 350

DK - Denmark 180

FI - Finland 3,425

IS - Iceland 0

NO - Norway 0


Question is:

How can we make the match function generic.

As as I move from May to June, the match reference changes. Making it absolute does not work as well.


Can anyone help please?


Thanks,

 

[Hui]

Tarun


Firstly, Welcome to the Chandoo.org forums.


I think your formula should be:

=INDEX($H$8:$K$14,MATCH($B9,$B$9:$B$14,0),Column()-Column($A1))

That way as you drag it across it will change the lookup Column automagically


I have assumed that the formula is in Column C

So Column()-Column($A1) = 2

If it is another Column adjust A1 so that the value of Column()-Column($A1) = 2

EG if it is in Column G use Column()-Column($E1) = 2