I may not have completely answered your question the first time.

The values I want to retrieve are unique, by nature(names, but not people
names)...but basically I want to find 1 value in the source range(multiple
numeric values, with varying duplicate quantities), and display a value 51
columns to the left of it(which happens to have unique text values) for every
instance of the selected value in the source range. I do not need to
manipulate it, once retrieved.

Hope that explains what I'm trying to accomplish a little better.

Regards,
Steve

"T. Valko" wrote:

So you want to extract the unique values. Are there any empty cells within
the source range?

Biff

"SteveMax" wrote in message
...
Hi,

I posted this question in the General Discussion forum, without realizing
it, so I reposted it here thinking it might get the right eyes in this
forum
perhaps.

I am trying to show an analysis of data in 1 worksheet(analysis
worksheet),
from another worksheet(data worksheet). The key data in which I am trying
to
reference has multiple values, and varying quantities of duplicates among
those multiple values, and in the formula it is named "UDP". In some
cases I have 15 instances of the same value, and in other instances, just
1.

The desired dataset which I'm deriving from the key data, is
a column of unique text values...I dont want to manipulate them in any
way,
just display them, so that I can perform some additional VLOOKUPS, and
calculate those results, in the analysis worksheet...so therefore, it is
desired to not repeat the same result, for more reasons than aesthetics.

Currently, using the OFFSET/MATCH function, I must keep the data worksheet
sorted by this key range(or at least keep the values contiguous), in order
to
display the results correctly in the multi-cell array I've created to
display
the multiple results(I could live with no sorting in the data worksheet,
but
not preferred).

I want to have the user select a data validated selection in the analysis
worksheet, which in turn creates the keyed criteria for the multi-cell
array(so far, this works well, except when the amount of resultant values
is
1), and displays the multiple results(51 columns to the left) of every
instance of this key data.

To clarify again, the key data has multiple values & duplicates, so the
amount of resultant data will vary, but will remain unique text. It works
acceptably with the OFFSET/MATCH combo for displaying in a multi-cell
array,
but repeats the same value when the results(or height in rows) is 1...it
functions great for more than 1 result. I have a possible workaround,
which
involves an IF statement for my analysis worksheet, but I'd rather not
have
to do that, if at all possible.

Here is the formula I've tried to use...where "UDP" is a named range in
the
analysis worksheet...and of course the {} are in the actual multi-cell
array.

=OFFSET(UDP,MATCH(O41,UDP,0)-1,-51,COUNTIF(UDP,O41),1)


Regards,
Steve