How do you achieve resolving the duplicate number problem
in Lookup. Such as:
Freq Number
40 29
38 43
37 7
36 20
36 20 <---- should be 58
35 13


such as 36 and 20 in the above. There are two freq with number 36.
How do you get VLookup to go past the first number 20, to the
second number Freq (36), which in this case should be number 58.

Any help would be appreciated.

Are you trying to retreive the value which is furthest down the lookup range
or the one with the highest value. If it is the highest value set the last
parameter of the formula to TRUE.

HTH
Matt

"smandula" wrote:

How do you achieve resolving the duplicate number problem
in Lookup. Such as:
Freq Number
40 29
38 43
37 7
36 20
36 20 <---- should be 58
35 13


such as 36 and 20 in the above. There are two freq with number 36.
How do you get VLookup to go past the first number 20, to the
second number Freq (36), which in this case should be number 58.

Any help would be appreciated.

For what you are trying to do Vlookup must reference only unique values. It
will only find the first instance of 36. There is no easy way to find the
second instance of 36 in the list.

You may want to consider a pivot table to create a hierarchy of the
frequencies and the numbers associated with those frequencies. If you are
unfamiliar with pivot tables or what I am suggesting just reply back.
--
HTH...

Jim Thomlinson


"smandula" wrote:

How do you achieve resolving the duplicate number problem
in Lookup. Such as:
Freq Number
40 29
38 43
37 7
36 20
36 20 <---- should be 58
35 13


such as 36 and 20 in the above. There are two freq with number 36.
How do you get VLookup to go past the first number 20, to the
second number Freq (36), which in this case should be number 58.

Any help would be appreciated.

smandula wrote:
How do you achieve resolving the duplicate number problem
in Lookup. Such as:
Freq Number
40 29
38 43
37 7
36 20
36 20 <---- should be 58
35 13


such as 36 and 20 in the above. There are two freq with number 36.
How do you get VLookup to go past the first number 20, to the
second number Freq (36), which in this case should be number 58.

Any help would be appreciated.




Assume that you name your frequency data column freq e.g A4:A9
Assume that your result data is in B4:B9

Assume you want to lookup the last occurrence of a frequency number in
the
freq range and return the value in column B, assume that there could be
no occurrences or many occurrences.

Assume that you put your search value in D3 e.g 36

Enter the following formula in D4

=IF(COUNTIF(freq,D3)=0,"No
Match",OFFSET(INDEX(freq,MATCH(D3,freq,-1),1),COUNTIF(freq,D3)-1,1))

Phillip

Thanks Phillip for your explanation. I like the formula idea.

but I am no further ahead as it comes back to being unable
in distinguishing a different number for the same frequency.

This is further revealed when you have 3 of the same frequencies.
40 29
38 43
37 7
36 20
36 20 <---- should be 58
36 20 <---- should be 61
35 13
It is either predicated on the first or the last largest.
Is there anyway to break out from this situation?

With Thanks