The following formula is in €œK€� and will return the value in €œA€� when the
conditions are met.

=INDEX(INDIRECT("$A$2:$A$5000"),MATCH(SMALL($H$2:$ H$5000,ROWS($IV$1:IV3)),$H$2:$H$5000,0))

Because €œA€� contains duplicate values these are returned to €œK€�
I want €œK€� to only return the same value once

What is the best way to achieve this?

=IF(COUNTIF($A$2:$A3,A3)=1,INDEX(INDIRECT("$A$2:$A $5000"),MATCH(SMALL($H$2:$H$5000,ROWS($IV$1:IV3)), $H$2:$H$5000,0)),"")

--
HTH,
Bernie
MS Excel MVP


"Gotroots" wrote in message
...
The following formula is in "K" and will return the value in "A" when the
conditions are met.

=INDEX(INDIRECT("$A$2:$A$5000"),MATCH(SMALL($H$2:$ H$5000,ROWS($IV$1:IV3)),$H$2:$H$5000,0))

Because "A" contains duplicate values these are returned to "K"
I want "K" to only return the same value once

What is the best way to achieve this?

Hi Bernie Deitrick

Your solution returned intermittent results, Records that should been
returned did not return.

"Bernie Deitrick" wrote:


=IF(COUNTIF($A$2:$A3,A3)=1,INDEX(INDIRECT("$A$2:$A $5000"),MATCH(SMALL($H$2:$H$5000,ROWS($IV$1:IV3)), $H$2:$H$5000,0)),"")

--
HTH,
Bernie
MS Excel MVP


"Gotroots" wrote in message
...
The following formula is in "K" and will return the value in "A" when the
conditions are met.

=INDEX(INDIRECT("$A$2:$A$5000"),MATCH(SMALL($H$2:$ H$5000,ROWS($IV$1:IV3)),$H$2:$H$5000,0))

Because "A" contains duplicate values these are returned to "K"
I want "K" to only return the same value once

What is the best way to achieve this?



.

Post a 4 row example of your data - say, rows two to five - including one
set of duplicates, and indicate what you are currently getting with your
formula and what you expect the formula to return.

Bernie


"Gotroots" wrote in message
...
Hi Bernie Deitrick

Your solution returned intermittent results, Records that should been
returned did not return.

"Bernie Deitrick" wrote:


=IF(COUNTIF($A$2:$A3,A3)=1,INDEX(INDIRECT("$A$2:$A $5000"),MATCH(SMALL($H$2:$H$5000,ROWS($IV$1:IV3)), $H$2:$H$5000,0)),"")

--
HTH,
Bernie
MS Excel MVP


"Gotroots" wrote in message
...
The following formula is in "K" and will return the value in "A" when
the
conditions are met.

=INDEX(INDIRECT("$A$2:$A$5000"),MATCH(SMALL($H$2:$ H$5000,ROWS($IV$1:IV3)),$H$2:$H$5000,0))

Because "A" contains duplicate values these are returned to "K"
I want "K" to only return the same value once

What is the best way to achieve this?



.

Here is a breakdown of the results I am getting

A3:A14 are entered values

A3 = apples
A4 = apples
A5 = pears
A6 = oranges
A7 = apples
A8 = grapefruit
A9 = grapes
A10 = lemons
A11 = bananas
A12 = bananas
A13 = kiwi
A14 = plums

B3:B14 are the result of a formula

B3 = no record
B4 = no record
B5 =
B6 =
B7 = no record
B8 =
B9 = no record
B10 = no record
B11 = no record
B12 = no record
B13 =
B14 = no record

C3:C14 are the result of a formula

C3 = 3
C4 = 4
C5 =
C6 =
C7 = 7
C8 =
C9 = 9
C10 = 10
C11 = 11
C12 = 12
C13 =
C14 = 14

D3 contains the formula;
=IF(COUNTIF($A$3:$A42,A4)=1,INDEX(INDIRECT("$A$3:$ A$5000"),MATCH(SMALL($C$3:$C$5000,ROWS($IV$1:IV1)) ,$C$3:$C$5000,0)),"")

D3:D14 contains the above formula fill down

D3 =
D4 = apples
D5 = apples
D6 =
D7 = lemons
D8 = bananas
D9 = bananas
D10 =
D11 =
D12 = #NUM!
D13 = #NUM!
D14 =

What the formula should have returned is as follows:

D3 = apples
D4 = grapes
D5 = lemons
D6 = bananas
D7 = plums
D8 =
D9 =
D10 =
D11 =
D12 =
D13 =
D14 =


"Bernie Deitrick" wrote:

Post a 4 row example of your data - say, rows two to five - including one
set of duplicates, and indicate what you are currently getting with your
formula and what you expect the formula to return.

Bernie


"Gotroots" wrote in message
...
Hi Bernie Deitrick

Your solution returned intermittent results, Records that should been
returned did not return.

"Bernie Deitrick" wrote:


=IF(COUNTIF($A$2:$A3,A3)=1,INDEX(INDIRECT("$A$2:$A $5000"),MATCH(SMALL($H$2:$H$5000,ROWS($IV$1:IV3)), $H$2:$H$5000,0)),"")

--
HTH,
Bernie
MS Excel MVP


"Gotroots" wrote in message
...
The following formula is in "K" and will return the value in "A" when
the
conditions are met.

=INDEX(INDIRECT("$A$2:$A$5000"),MATCH(SMALL($H$2:$ H$5000,ROWS($IV$1:IV3)),$H$2:$H$5000,0))

Because "A" contains duplicate values these are returned to "K"
I want "K" to only return the same value once

What is the best way to achieve this?



.


.