nicolelost
New Member
- Joined
- Jan 13, 2013
- Messages
- 3
I have been working on this for days and have searched and searched. Of the forums where I have found help this has been the best.
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.
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.