Index and Small

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BoRed79 - 09 Jul 2007 16:06 GMT

I am using the following formula (and varients of it) to produce a table
showing the top two and bottom two locations (the place names and their
relevant percentages):

=INDEX('Q4'!H5:H36,MATCH(LARGE('Q4'!H5:H36,1),'Q4'!H5:H36,0))

In the main they work fine, however, if I have two values the same then it
will not bring back the second label - it just repeats the same as either the
highest or lowest (whichever it matches).

Is there a varient to the formula to be able to look at identical values??

Thanks.

Reply to this Message

Bernie Deitrick - 09 Jul 2007 17:12 GMT

To get a value from another range (G5:G36) that matches the largest value in H5:H36:
=INDEX(G5:G36,MATCH(LARGE('Q4'!H5:H36,1),'Q4'!H5:H36,0))

To get a value from another range (G5:G36) that matches the second largest value in H5:H36, even if
it is a repeat of the largest value:
=IF(INDEX(G5:G36,MATCH(LARGE('Q4'!H5:H36,1),'Q4'!H5:H36,0))=INDEX(G5:G36,MATCH(LARGE('Q4'!H5:H36,2),'Q4'!H5:H36,0)),INDEX(OFFSET('Q4'!G5:G36,MATCH(LARGE('Q4'!H5:H36,1),'Q4'!H5:H36,0),0),MATCH(LARGE('Q4'!H5:H36,2),OFFSET('Q4'!H5:H36,MATCH(LARGE('Q4'!H5:H36,1),'Q4'!H5:H36,0),0),0)),INDEX(G5:G36,MATCH(LARGE('Q4'!H5:H36,2),'Q4'!H5:H36,0)))

HTH,
Bernie
MS Excel MVP

>I am using the following formula (and varients of it) to produce a table
> showing the top two and bottom two locations (the place names and their
[quoted text clipped - 9 lines]
>
> Thanks.

Reply to this Message

Bernie Deitrick - 09 Jul 2007 17:22 GMT

Instead of saying "matches" I should have said "corresponds to" Sorry for being unclear....

Bernie
MS Excel MVP

> To get a value from another range (G5:G36) that matches the largest value in H5:H36:
> =INDEX(G5:G36,MATCH(LARGE('Q4'!H5:H36,1),'Q4'!H5:H36,0))
[quoted text clipped - 20 lines]
>>
>> Thanks.

Reply to this Message

BoRed79 - 10 Jul 2007 16:44 GMT

Unfortunately, this brings back a #N/A error.

> Instead of saying "matches" I should have said "corresponds to" Sorry for being unclear....
>
[quoted text clipped - 25 lines]
> >>
> >> Thanks.

Reply to this Message

Bernie Deitrick - 10 Jul 2007 16:59 GMT

BoRed79,

It doesn't for me. I will send you a working example if you email me privately - take out the
spaces and make the logical substitutions to create my address.

HTH,
Bernie
MS Excel MVP

> Unfortunately, this brings back a #N/A error.
>
[quoted text clipped - 28 lines]
>> >>
>> >> Thanks.

Reply to this Message

Rate this thread: