Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two way lookup
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 <bthree</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(RAN GE)),ROW(1:1)), 2)),"",INDEX(RANGE,SMALL(IF(RANGE=A2,ROW(RANGE)),R OW(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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two way lookup
See your other post
-- Biff Microsoft Excel MVP wrote in message ... 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 <bthree</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(RAN GE)),ROW(1:1)), 2)),"",INDEX(RANGE,SMALL(IF(RANGE=A2,ROW(RANGE)),R OW(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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions |