Question regarding HLookup (unique values)

[J]

I have a 33 column, 2 row array. The first row contains names. The second row
contains values between 0-20. I use HLookup to find a name that matches a
number and return the name. I do this down another column of 33 fields so it
lists every name. The problem is if HLookup finds a value, it will return the
same name multiple times (depending on how often the value appears)

Example:
CD88 - 6
CD89 - 6
CD90 - 5

=HLOOKUP(CD88,$AP$87:$BV$88,2,FALSE)

For instance, Person A and Person B both have values of 6, Person C has a
value of 5. When my HLookup function gets to CD88, it returns person A. The
second row Hlookup looks now for CD89 (which is also 6). Instead of returning
Person B, it returns Person A again.

How can I set up my function to skip over names it has already selected so I
get all 33 unique names.

Thanks,

 

[Conan Kelly]

J,

You are going to need a unique identifier for each person in order to use
HLOOKUP. If Person A & B both have a value of 6, it will only return the
first occurence of 6.

It might be possible to do it adding other functions to your formula, but
that will get a little convoluted.

Is there any way to add a "suffix" to your person identifer.......maybe
something like 6.1 & 6.2 or 6-1 & 6-2.

If you can not change the way things are done, it is a little beyond me to
help you out. Maybe someone more knowlegable can help you.

Sorry,

Conan

 

[T. Valko]

Do you have *all* 33 numbers listed in a column? If so, are they in order?
Ascending? Descending?

 

[J]

The 33 numbers are listed in a row. They change over time. At the beginning,
many people have the same number identifier. After about 13-14 trials, it's
pretty unlikely any of the numbers would be repeats (these numbers start at 0
and go up to about 20 million). They are in no particular order because they
can change at any point.

Thoughts?

 

[J]

Well here's the thing. I'm also using the LARGE function to order the numbers
in another column. If adding a suffix would mess that up, then I don't think
I can add a suffix

 

[RagDyer]

I believe your description is wrong.
If the *first* row contains names, Hlookup will *not* lookup values in the
second row, and return names from the first row.

Assuming that was a typo, and values are in AP87 to BV87,
With names in AP88 to BV88, try this *array* formula:

=INDEX($AP$88:$BV$88,LARGE(IF($AP$87:$BV$87=CD88,COLUMN($A:$AG),""),COUNTIF(CD88:$CD$120,CD88)))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE entry, copy down as needed.

 

[J]

The description was correct. Instead of 1, I tell HLOOKUP to look in the
SECOND column. I'll try that formula thanks

 

[T. Valko]

I'm also using the LARGE function to order the numbers in another column

Well, now I'm confused!

Let's try this:

AP87:BV87 = names
AP88:BV88 = nums

You have this formula entered in CD88 copied down to CD120 (33 rows):

=LARGE(nums,ROWS(CD$88:CD88))

Enter this array formula** in CE88 and copy down to CE120:

=INDEX(names,SMALL(IF(nums=CD88,COLUMN(names)-MIN(COLUMN(names))+1),COUNTIF(CD$88:CD88,CD88)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)