#### nicolelost

##### New Member

- Joined
- Jan 13, 2013

- Messages
- 3

I have two columns of data:

Name: Performance Value:

A 1

B 2

C 0

D 2

E 5

F 6

G 3

In another spot I am trying to lookup the following:

1) the top 5 Performance Values2) The lowest 5 Performance Values when the value is greater than 0

3) The Name Associated with the top or lowest performance value, accounting for duplicates

4) I would like to set it up to where I have a cell where I put in the cell range to use in the formula and source back to it, I have no Idea how to do this or search for it apparently. I have to copy and paste all this numerous times so I need to make it easy to modify these formulas by changing the cell range.

Here is how I have it set up:

Name Lookup: Smallest or Largest Values:

=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =SMALL(RangeWithPerformanceValues,1+COUNTIF(RangeWithPerformanceValues,0))

=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =SMALL(RangeWithPerformanceValues,2+COUNTIF(RangeWithPerformanceValues,0))

=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =SMALL(RangeWithPerformanceValues,3+COUNTIF(RangeWithPerformanceValues,0))

=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =SMALL(RangeWithPerformanceValues,4+COUNTIF(RangeWithPerformanceValues,0))

=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =SMALL(RangeWithPerformanceValues,5+COUNTIF(RangeWithPerformanceValues,0))

=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =LARGE(G3Three,5)

=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =LARGE(G3Three,4)

=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =LARGE(G3Three,3)

=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =LARGE(G3Three,2)

=INDEX(RangeWithNames,MATCH(CellToRight,RangeWithPerformanceValues,0)) =LARGE(G3Three,1)

Problems:

1) The name lookup formula cannot account for duplicates in the smallest or largest values. So if I have one that is valued at 2 and the next smallest is also 2 it still assigns the name for the first 2 found.

2) I have no idea how to fix it up to where I do not have to replace the range name by hand. I would like to be able to just change it once as I will use that range name in numerous formulas and then paste it elsewhere and need it to use a different range name.