Index and Small

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Instead of saying "matches" I should have said "corresponds to" Sorry for being unclear....

Bernie
MS Excel MVP
 
Unfortunately, this brings back a #N/A error.

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

Bernie
MS Excel MVP
Click to expand...
 
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
 
Back
Top