Looking up data in multiple columns

[mrjordan]

Suppose I have the following data arranged into 6 columns. Joe is assigned number 1, James is number 10, etc. I wish to be able to look up a number such as 6 in the following array and have it return the value one column over, in this case "Sam". I have experimented with the excel lookup, vlookup, index, and match functions for quite some time, and I still have not been able to find a solution. In this particular application, there will be 50 columns of people and their associated numbers, so a quick fix such as ISERROR for one lookup array, then moving on to the next will not work, I cant have 50 embedded ISERRORs. Any suggestions?

	A	B	C	D	E	F
1	Number	Person	Number	Person	Number	Person
2	1	Joe	5	Rob	9	Richard
3	2	Bob	6	Sam	10	James
4	3	Frank	7	Pete	11	John
5	4	Jim	8	Dan	12	Earl

<tbody>
</tbody>

 

[Smitty]

Welcome to the Board!

Why not just put your Number/Person in 2 columns?

 

[mrjordan]

I will have 50 columns of people and their associated numbers and each column will have 100 names and numbers. Putting all this in a1 and a2 will not work.

 

[Smitty]

Unfortunately, that's not a logical data structure. Is there a reason it needs to be this way?

 

[markmzz]

Hi Mrjordan,

Try this:

Layout

Number	Person	Number	Person	Number	Person		Number	Person
1	Joe	5	Rob	9	Richard		1	Joe
2	Bob	6	Sam	10	James		6	Sam
3	Frank	7	Pete	11	John		12	Earl
4	Jim	8	Dan	12	Earl		13
*******	*******	*******	*******	*******	*******	**	*******	*******

<tbody>
</tbody>

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

I2-> =IFERROR(INDEX($A$2:$F$5,
LARGE(IF(ISNUMBER(FIND("|"&$H2&"|","|"&$A$2:$E$5&"|")),ROW($A$2:$E$5)-ROW($A$2)+1),1),
LARGE(IF(ISNUMBER(FIND("|"&$H2&"|","|"&$A$2:$E$5&"|")),COLUMN($A$2:$E$5)-COLUMN($A$2)+2),1)),"")

Markmzz

 

[Marcelo Branco]

Hi,
IMHO i think is better arrange your data as suggested by Smitty.
Doing so you would avoid complex formulas, simply using VLOOKUP or INDEX/MATCH (regular formulas).

If you want a macro to arrange your data in two columns, maybe this
- Assumes data in Sheet1 beginning in column A, headers in row 1 (Adjust FirstCell in the code)
- Arranged data placed in Sheet2
***Try it on a copy of your workbook***

Sub arrData()
    Dim rngData As Range, lastRow As Long, lastColumn As Long, FirstCell As Range
    Dim dict As Object, rcell As Range, i As Long
    
    With Sheets("Sheet1")
        lastRow = .Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Row
               
        lastColumn = .Cells.Find(What:="*", SearchOrder:=xlColumns, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Column
            
        Set FirstCell = .Range("A2") '<--Adjust
        
        Set rngData = Range(FirstCell, .Cells(lastRow, lastColumn))
        
        Set dict = CreateObject("Scripting.dictionary")
        dict.comparemode = vbTextCompare
               
        On Error Resume Next
        For i = 1 To lastColumn Step 2
            For Each rcell In rngData.Columns(i).Cells
                If rcell <> "" Then dict.Add rcell.Value, rcell.Offset(0, 1).Value
            Next rcell
        Next i
        On Error GoTo 0
                
    End With
    
    With Sheets("Sheet2")
        .Range("A1") = "Number"
        .Range("B1") = "Person"
        .Range("A2").Resize(dict.Count) = Application.Transpose(dict.keys)
        .Range("B2").Resize(dict.Count) = Application.Transpose(dict.items)
    End With
End Sub

Example

Sheet1

	A​	B​	C​	D​	E​	F​
1​	Number​	Person​	Number​	Person​	Number​	Person​
2​	1​	Joe​	5​	Rob​	10​	Richard​
3​	2​	Bob​	6​	Sam​	11​	James​
4​	3​	Frank​	7​	Pete​	12​	John​
5​	4​	Jim​	8​	Dan​	13​	Earl​
6​			9​	Anthony​	14​	Mike​
7​					15​	Mary​

<tbody>
</tbody>

Sheet2 after macro

	A​	B​
1​	Number​	Person​
2​	1​	Joe​
3​	2​	Bob​
4​	3​	Frank​
5​	4​	Jim​
6​	5​	Rob​
7​	6​	Sam​
8​	7​	Pete​
9​	8​	Dan​
10​	9​	Anthony​
11​	10​	Richard​
12​	11​	James​
13​	12​	John​
14​	13​	Earl​
15​	14​	Mike​
16​	15​	Mary​

<tbody>
</tbody>

M.

 

[markmzz]

Another way (with OFFSET function):

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

I2-> =IFERROR(OFFSET($A$2,
LARGE(IF(ISNUMBER(FIND("|"&$H2&"|","|"&$A$2:$E$5&"|")),ROW($A$2:$E$5)-ROW($A$2)),1),
LARGE(IF(ISNUMBER(FIND("|"&$H2&"|","|"&$A$2:$E$5&"|")),COLUMN($A$2:$E$5)-COLUMN($A$2)+1),1)),"")

Do some tests with your data.

Markmzz

 

[mrjordan]

Thank you Markmzz, works great

 

[markmzz]

Thank you Markmzz, works great

You're welcome and I'm glad that your problem was solved.

Markmzz