Lookup Issue

[greg.vassar]

Hi group,
I've got an issue that's been bugging me for the past few days and no
amount of searching has been able to help yet. I appreciate any help
this group is able to provide in advance.

Goal: To display up to the first <b>three</b> users assigned to a
site. If there are no users, nothing is returned (no #NA, etc...)
Problem: First username is always returned in the first user column,
regardless of whether or not the site ID matches.


I have an Excel worksheet with mutiple sheets. One of the sheets
contains a list of uniquely named users and a non unique site ID
number. Lots of these users will have identical site numbers but
it's
all sorted by site in ascending order. In this case, site id is
column E and users is column F.


Site User
1 SmithJ
2 JohnsonB
2 AllisonK
4 ThomasT
4 SmithJ2
5 OlsonM
6 SmittyA


Another sheet has a unique list of site numbers (1,2,3,4,5...) again
in ascending order. To the right of this I would like to display the
usernames associated with the site. If there are no matches, it
should be blank. If there are more than three, those are ignored.
So, based on the example above, I would like to display:


Site User1 User2 User3
1 SmithJ
2 JohnsonB AllisonK
3
4 ThomasT SmithJ2
5 OlsonM
6 SmittyA


So far, I can get everything to display perfectly, with one
exception,
using the following formula. A2=Site Number
RANGE=List of Users on other sheet (E2:F8)


{=IF(ISERROR(INDEX(RANGE,SMALL(IF(RANGE=A2,ROW(RANGE)),ROW(1:1)),
2)),"",INDEX(RANGE,SMALL(IF(RANGE=A2,ROW(RANGE)),ROW(1:1)),2))}


The problem is that the first User1 field for the first site displays
the first User listed, regardless of whether or not the site number
matches. This user name will also be correctly displayed where the
site number matches up. So, if my site numbers were actually
0,1,2,3,4,5,6 then the incorrect result I'm seeing is:


Site User1 User2 User3
0 SmithJ
1 SmithJ
2 JohnsonB AllisonK
3
4 ThomasT SmithJ2
5 OlsonM
6 SmittyA


This is rather lengthy and a confusing post so if you're willing to
offer me advice, I'll gladly forward my spreadsheet to you.

 

[Ron Rosenfeld]

Hi group,
I've got an issue that's been bugging me for the past few days and no
amount of searching has been able to help yet. I appreciate any help
this group is able to provide in advance.

Goal: To display up to the first <b>three</b> users assigned to a
site. If there are no users, nothing is returned (no #NA, etc...)
Problem: First username is always returned in the first user column,
regardless of whether or not the site ID matches.


I have an Excel worksheet with mutiple sheets. One of the sheets
contains a list of uniquely named users and a non unique site ID
number. Lots of these users will have identical site numbers but
it's
all sorted by site in ascending order. In this case, site id is
column E and users is column F.


Site User
1 SmithJ
2 JohnsonB
2 AllisonK
4 ThomasT
4 SmithJ2
5 OlsonM
6 SmittyA


Another sheet has a unique list of site numbers (1,2,3,4,5...) again
in ascending order. To the right of this I would like to display the
usernames associated with the site. If there are no matches, it
should be blank. If there are more than three, those are ignored.
So, based on the example above, I would like to display:


Site User1 User2 User3
1 SmithJ
2 JohnsonB AllisonK
3
4 ThomasT SmithJ2
5 OlsonM
6 SmittyA


So far, I can get everything to display perfectly, with one
exception,
using the following formula. A2=Site Number
RANGE=List of Users on other sheet (E2:F8)


{=IF(ISERROR(INDEX(RANGE,SMALL(IF(RANGE=A2,ROW(RANGE)),ROW(1:1)),
2)),"",INDEX(RANGE,SMALL(IF(RANGE=A2,ROW(RANGE)),ROW(1:1)),2))}


The problem is that the first User1 field for the first site displays
the first User listed, regardless of whether or not the site number
matches. This user name will also be correctly displayed where the
site number matches up. So, if my site numbers were actually
0,1,2,3,4,5,6 then the incorrect result I'm seeing is:


Site User1 User2 User3
0 SmithJ
1 SmithJ
2 JohnsonB AllisonK
3
4 ThomasT SmithJ2
5 OlsonM
6 SmittyA


This is rather lengthy and a confusing post so if you're willing to
offer me advice, I'll gladly forward my spreadsheet to you.

In the original data, I named the Site column "Site" and the user column "User"


I set up a table as follows:

I1: Site
J1: User1
K1: User2
L1: User3

I2: 1
I3: 2
.... ...
I7: 6

These formulas seem to produce the table you want:

J2: =IF(ISNA(LOOKUP(2,1/(Site=$I2),User)),"",LOOKUP(2,1/(Site=$I2),User))

K2:
=IF(ISNA(LOOKUP(2,1/((Site=$I2)*(User<>J2)),User)),"",
LOOKUP(2,1/((Site=$I2)*(User<>J2)),User))

L2:
=IF(ISNA(LOOKUP(2,1/((Site=$I2)*(User<>J2)*(User<>K2)),User)),"",
LOOKUP(2,1/((Site=$I2)*(User<>J2)*(User<>K2)),User))

Select J2:L2 and fill down as far as required -- in your example to I7


--ron

 

[T. Valko]

it's all sorted by site in ascending order.

Here's a small sample file that demonstrates this:

http://cjoint.com/?cvuenLnUgy

 

[greg.vassar]

Biff and Ron,
Both solutions work great. Thanks for the help.

Greg

 

[Ron Rosenfeld]

Biff and Ron,
Both solutions work great. Thanks for the help.

Greg

Glad to help.

Thanks for the feedback.
--ron

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Biff and Ron,
Both solutions work great. Thanks for the help.

Greg