Quote:
Originally Posted by Nick
I want to lookup values where duplicates exist but do not want the same
lookup result.
e.g.
1 12
2 35
3 12
4 14
5 13
6 35
How can I use Match, Index to return 1 when looking up 12, then 3 when
looking up 12, what happens now is I lookup 12 and get 1 every time. I've
used pivot tables and that works but its not ideal.
Thanks for all prev help.
Nick
|
SOLVED
After a bit of work, I have determined how to handle this situation with resorting to array formulas and that can handle ANY number of duplicates. The goal is to get the correct ROW number via a MATCH formula.
Usually this situation results when you want to SORT via the LARGE or SMALL formula and find the row number to look up another value via INDEX.
Assume Column A and B contain the above values. (we are not really using column A so we are sorting Column B
And assume the top row is row 2 (values 1, 12) [We must have a blank row above our data]
First, create a third column beside the first two with the following formula
=LARGE($B$2:$B$7,ROW(A1)) [create in top cell, fill down]
The values in this column should be
35
35
14
13
12
12 (as you can see, we have two sets of duplicates)
Next create a fourth column beside the third column with the following formula
=MATCH(c1,$B$2:$B$7,0) [create in top cell, fill down]
The values in this column should be
2
2 (duplicate row)
4
5
1
1 (duplicate row)
Finally create a fifth column with the following formula ((NOTE/WARNING: E1 MUST BE ZERO OR BLANK). It can not contain a header!!!
[create in top cell, fill down] =IF(D1<D2,D2,MATCH(C2,OFFSET($B$2:$B$7,E1,0,ROWS( $B$2:$B$7)-E1),0)+E1)
The values in this column should be
2
6
4
5
1
3
As you can see, no duplicate values at all. And this will work regardless of the number of duplicates.