Index and Match - Max formula lookup the highest value

**ghostexcel** · 03-12-2015, 12:43 PM

Hi everyone,

I wonder if someone could suggest the best way to create a VBA code for this example:

Screen Shot 2015-03-12 at 16.40.09.png

I would like to enter a letter in B9 (the yellow one) and getting the top three values.

Is about using Index and Match - Max value formula, but do not know how to use it in VBA.

Many thanks

**AlKey** · 03-12-2015, 12:51 PM

Try this

=INDEX($B$2:$F$5,MATCH($B$9,$A$2:$A$5,0),MATCH($C9,$B$1:$F$1,0))

Row\Col	A	B	C	D	E	F
1		Store 1	Store 2	Store 3	Store 4	Store 5
2	A	45	20	26	62	39
3	B	32	60	52	99	55
4	C	65	0	39	32	99
5	D	45	28	9	17	42
6
7
8
9		A	Store 1	45
10			Store 4	62
11			Store 2	20

**bebo021999** · 03-12-2015, 01:10 PM

In D9:

=LARGE(OFFSET($B$1:$F$1,MATCH($B$9,$A$2:$A$5,0),),ROWS($1:1))

C9:

=INDEX($B$1:$F$1,MATCH(D9,OFFSET($B$1:$F$1,MATCH($B$9,$A$2:$A$5,0),),0))

Drag down