Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Index Match Function For Two Same Values

1. Senior Member
Join Date
12th March 2007
Posts
128

## Index Match Function For Two Same Values

I am using Index & Match function to get the desired values where in from a table Those having largest percentage gain/loss is extracted

suppose the data are as follows
SCRIP A 3.2
SCRIP B 2.55
SCRIP C 0.35
SCRIP D 1.1
SCRIP E 0
SCRIP F 2.95
SCRIP G 0.5
SCRIP H 1.05
SCRIP I 0.45
SCRIP J 2.55
SCRIP K 3.8
SCRIP L 2.9

The result will be
SCRIP K 3.8
SCRIP A 3.2
SCRIP F 2.95
SCRIP L 2.9
SCRIP B 2.55
SCRIP B 2.55

SCRIP K 3.8
SCRIP A 3.2
SCRIP F 2.95
SCRIP L 2.9
SCRIP B 2.55
SCRIP J 2.55

Why? what I am gusseing is that the formula is taking the result on ascenting order basis. Is this the limitation of Index/Match, if yes, then what can I do to get the correct result

The forn=mula i am using for getting the scrip name
=INDEX(Summary!\$B\$2:\$I\$13,MATCH(B3,Summary!\$I\$2:\$I\$13,0),1)
where "Summary" is the name of the sheet

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Index Match Function For Two Same Values

Hi,

Try,

In J2 on Summary and copied down,

=RANK(I2,\$I\$2:\$I\$13)+COUNTIF(\$I\$2:I2,I2)-1

=INDEX(Summary!B\$2:B\$13,MATCH(RANK(\$B3,\$B\$3:\$B\$8)+COUNTIF(\$B\$3:\$B3,\$B3)-1,Summary!\$J\$2:\$J\$13,0))

HTH

3. Senior Member
Join Date
12th March 2007
Posts
128

## Re: Index Match Function For Two Same Values

The solution is not solving the problem
Sorry that the data was not showing properly in the post and as the file was showing big size even after zipping, so I could not att the file also. Now I have extracted some part and posting the file. Help this will clear my doubt & qwery.

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Index Match Function For Two Same Values

Hi,

See the attachment.

5. Senior Member
Join Date
12th March 2007
Posts
128

## Re: Index Match Function For Two Same Values

Nice, really very nice!
Thanks

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno