Find 2nd, 3rd, etc Result with index/match

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BKO - 13 Jun 2007 11:11 GMT

To lookup data I use INDEX MATCH in an unsorted sheet,

I can only find the 1st Result,

How con I look for 2nd, 3rd, etc result

I tried looking in other post, but I couldn't find anything

Any help would be appreciated very much

Signature

There are only 10 types of people in the world:
Those who understand binary and those who don''''t.

Reply to this Message

Mike H - 13 Jun 2007 13:59 GMT

Hi,

Try this:-

=INDEX($A$2:$F500,SMALL(IF($A$2:$F500=$G$1,ROW($A$2:$F500)-ROW($A$2)+1,ROW($F500)+1),4),2)

This looks up a value found in G1 in Column A of an Array A2-F500.

The last 2 numbers are the significant ones. The 2 tells it to rurn the
value from column 2 and the 4 tell it to return the 4th instance of the value
in G1. Change the for to suit. It's an array so Ctrl+Shift+Enter

Mike

> To lookup data I use INDEX MATCH in an unsorted sheet,
>
[quoted text clipped - 5 lines]
>
> Any help would be appreciated very much

Reply to this Message

bj - 13 Jun 2007 14:00 GMT

Assuming your data is in A1:G100
and you want to put your match in H1
and the Match list in AA1:AG??

in Z1
=if(countif(A:A,H1)<Row(),"",match(Z1,A1:A100,0))
in Z2
=if(countif(A:A,$H1)<Row(),"",$Z$1+match($Z$1,Offset($A$1,Z1,0,100-Z1))

copy Z2 and p[aste down to more rows than you expect to have matches
in AA1
Enter
=if($Z1="","",index(AA$1, Z$1,1)
Copy and paste to AG??

alternately you might be able to use advanced filter

> To lookup data I use INDEX MATCH in an unsorted sheet,
>
[quoted text clipped - 5 lines]
>
> Any help would be appreciated very much

Reply to this Message