Lookup across multiple columns with lists in cells!!!

[sxhall]

Hi,

I have a range of data across three columns that contain numbers separated by commas. To left I have list of employee numbers that I need to reference back to when an instance of a number is found. To compound this i need to return all employee numbers when an instance of a number is found!!!

To hopefully make it easier to understand I have attempted an example...

Emp_______l__List_l__List__l_List__l__
Number____l__One_l__Two_l Three_l__

1112______l__1,2_ l_8_____l_7____ l__
1113______l__2___l_7_____l_6,5___ l__
1114______l__4___l_2_____l_8_____l__
1115______l__1,5_ l_9,10__l_11____l__


So for example if I typed 2 in to cell A1 in cell B1 it would return '1112,1113,1114' , a 7 would return '1112,1113' and 10 would only return '1115'

Not adverse to having another table in the spreadsheet to assist in getting the result as well. I have been at this for several hours now and just can't figure a way to get it work with additional tables and formulas combined together and am at the point of frustration!!!!

Thanks in advance for any help.

Steven

PS apologies for the crude example, but I don't know how to attach a proper example to a post.

 

[skorpionkz]

can you use UDF?

 

[sxhall]

can you use UDF?

I could but would not know where to start with it!

 

[Joyner]

Hello are there only three lists as you show (List one, two and three)?

And how many employee numbers can be returned in cell B1. If there are many, putting them all in one cell, separated by commas doesn't sem to be the most efficent way. But if that is what you need, let me know.

 

[sxhall]

Hello are there only three lists as you show (List one, two and three)?

And how many employee numbers can be returned in cell B1. If there are many, putting them all in one cell, separated by commas doesn't sem to be the most efficent way. But if that is what you need, let me know.

Hi Joyner,

Thanks for looking at this.

I currently only have three lists against the employee numbers that need to be searched through. And I doubt it will change, given the criteria they represent, to more than that so could be restricted to cover only three lists.

The list of employee numbers will only ever have one employee number contained within a cell, although it may be duplicated within the list. However it will only need to be returned once in the result if matched to a number in any of the lists.

I have tried to split out the lists (list one, two & three) in to several columns but not knowing how many numbers will be contained in the data in the first instance means I would be guessing at the number of additional columns required. Would also mean adding in more formulas that will not return a value in the majority of cases as well.

If I can offer any more detail let me know.

Steven

 

[skorpionkz]

Sorry it took to long try this UDF

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the UDF type =UDF name.

UDF name here is ArrayLookup
you need to provide 2 parameters
1. value you want to search
2. range in which you want to look for values (do not include the EMP numbers in it)

Public Function ArrayLookup(LookUp_Value As String, LookUp_Range As Range) As String

Dim varTest As Variant
Dim iCount As Integer
Dim irow As Integer
Dim srow As Long, erow As Long, scol As Long, ecol As Long, x As Long
Dim myStr As String, strEMP As String

srow = LookUp_Range.Rows(1).Row
erow = LookUp_Range.Rows.Count + srow - 1
scol = LookUp_Range.Columns(1).Column
ecol = LookUp_Range.Columns.Count + scol - 1
strEMP = Empty

For irow = srow To erow
    myStr = Empty
    For Each varTest In Range(Cells(irow, scol), Cells(irow, ecol))
        If myStr = Empty Then
            myStr = "|" & Trim(varTest) & "|"
        Else
            myStr = myStr & Trim(varTest) & "|"
        End If
    Next varTest
    
    myStr = Replace(myStr, ",", "|")
    myStr = Replace(myStr, " ", "")
    
    If myStr Like "*|" & Cells(1, 1) & "|*" Then
        If strEMP = Empty Then
            strEMP = Cells(irow, scol - 1)
        Else
            strEMP = strEMP & ", " & Cells(irow, scol - 1)
        End If
    End If
Next irow

ArrayLookup = strEMP

End Function

Excel 2010
	A	B	C	D
1	2	1112, 1113, 1114
2
3	Emp Number	List One	List Two	List Three
4	1112	1,2	8	7
5	1113	2	7	6,5
6	1114	4	2	8
7	1115	1,5	9,10	11
Sheet1

Cell Formulas
Range	Formula
B1	=ArrayLookup($A$1,B4:D7)

 

[sxhall]

Sorry it took to long try this UDF

That was not long at all skorpionkz, I'd been stuck on it for hours!

This worked excellent given my sample data however in my example I simplified it in the fact that cell A1 would be used and where the ranges sat. When I transferred in to my actual workbook with the real data in it produced an odd result as my search cell was U16 and the range is AH9:AJ52.

Can the UDF be changed to work on a random entry of lookup cell v range? Only as I change the worksheet (as I know it will over time) the lookup up cell will move in relation the lookup range.

Also I will need to be able to list all of the values to be searched for in a column and drag this UDF down against them all to return results...

Criteria__lEmployee Numbers
1________l1112,1115_______
2________l1110 ___________
3________l _______________
4________l1176,1112_______

As an example

Thanks again

Steven

 

[skorpionkz]

try this updated UDF.

you need to provide 3 parameters here:
1. value you want to search
2. range in which you want to look for values (do not include the EMP numbers in it)
3. range which contains results value

NOTE: the number of rows in (2) and (3) HAS to be same!!!

Public Function ArrayLookup(LookUp_Value As String, LookUp_Range As Range, LookUp_Results As Range) As String


Dim varTest As Variant
Dim iCount As Integer
Dim irow As Integer
Dim srow As Long, erow As Long, scol As Long, ecol As Long, rcol As Long, x As Long
Dim myStr As String, strEMP As String


If Not LookUp_Range.Rows.Count = LookUp_Results.Rows.Count _
And Not LookUp_Range.Rows(1).Row = LookUp_Results.Rows(1).Row Then
    ArrayLookup = CVErr(xlErrRef)
    Exit Function
End If


srow = LookUp_Range.Rows(1).Row
erow = LookUp_Range.Rows.Count + srow - 1
scol = LookUp_Range.Columns(1).Column
ecol = LookUp_Range.Columns.Count + scol - 1
rcol = LookUp_Results.Column
strEMP = Empty


For irow = srow To erow
    myStr = Empty
    For Each varTest In Range(Cells(irow, scol), Cells(irow, ecol))
        If myStr = Empty Then
            myStr = "|" & Trim(varTest) & "|"
        Else
            myStr = myStr & Trim(varTest) & "|"
        End If
    Next varTest
    
    myStr = Replace(myStr, ",", "|")
    myStr = Replace(myStr, " ", "")
    
    If myStr Like "*|" & LookUp_Value & "|*" Then
        If strEMP = Empty Then
            strEMP = Cells(irow, rcol)
        Else
            strEMP = strEMP & ", " & Cells(irow, rcol)
        End If
    End If
Next irow


ArrayLookup = strEMP


End Function

Excel 2010
	A	B	C	D
1	Emp Number	List One	List Two	List Three
2	1112	1,2	8	7
3	1113	2	7	6,5
4	1114	4	2	8
5	1115	1,5	9,10	11
Sheet1

Excel 2010
	A	B
14	1	1112, 1115
15	2	1112, 1113, 1114
16	3
17	4	1114
18	5	1113, 1115
19	6	1113
20	7	1112, 1113
21	8	1112, 1114
22	9	1115
23	10	1115
24	11	1115
25	12
26	13
Sheet1

Cell Formulas
Range	Formula
B14	=ArrayLookup(A14,$B$2:$D$5,$A$2:$A$5)

 

[sxhall]

try this updated UDF.

you need to provide 3 parameters here:
1. value you want to search
2. range in which you want to look for values (do not include the EMP numbers in it)
3. range which contains results value

NOTE: the number of rows in (2) and (3) HAS to be same!!!

Superb! Exactly what I am after.

Thank you very much

Steven

 

[sxhall]

Andrew,

Another problem i found is that the formula is not always refreshing when I change the search criteria! I am having to go in to each cell and then click return for the cell to recalculate. I have excel set to auto calculate and using F9 as a prompt is not working either.

Any clues as to what may be stopping it from refreshing the formula's result when I change the search criteria?

Thanks

Steven