Two way lookup

[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.

 

[Dave D-C]

Maybe this is a copout to resort to a UDF (User Defined Function):
Assume rngSite is your list of sites, Sheetx!E2:E8, and
the users are in Sheetx!F2:F8

In Module1:
Public Function FuncDC1$(rng1 As Range, rng2 As Range)
Dim Nth%, Ith%, zCell As Range
Nth = rng2.Column - rng1.Column
For Each zCell In Range("rngSite")
If zCell.Value = rng1.Value Then Ith = Ith + 1
If Ith = Nth Then
FuncDC1 = zCell.Offset(0, 1).Value
Exit Function
End If
Next zCell
FuncDC1 = ""
Exit Function
End Function ' D-C

In A2 and down, put your site numbers.
In B2, put =FuncDC1($A2,B2)
and copy down and over.

 

[T. Valko]

See your other post