CODEWITHSUNDEEP
excelvba
Barry Rosenberg
Barry Rosenberg

Reputation: 278

Lookup across multiple columns & rows in excel

Vlookup's limitation is that it searches for a value down a single column. I need to search across multiple columns and multiple rows.

I have data in the following format:

HOST1    Guest1    Guest2    Guest3    Guest4
HOST2    Guest5    Guest6    Guest7    Guest8

I need to convert it down to two column pairs like this:

Guest1    Host1
Guest2    Host1
Guest3    Host1

So I want to lookup the guest's name in the range, b1:e2 in the first example. Then grab the row number, and grab the value of {A$rownumber}.

Is it possible to do this kind of multicolumn, multirow search? Or are all the searches limited to one-dimensional vectors?

Upvotes: 1

Views: 3706

Answers (2)

Brad
Brad

Reputation: 12255

I made a user form with a combobox called ComboBox1 and a textbox called TextBox1 (multiline property enabled). When you load the user form (UserForm1) you the Initialize event will fire executing UserForm_Initialize(). That will populate the combobox with all the hosts. When you select a host the ComboBox1_Change() event will fire and output something like HOST2 Guest5 Guest6 Guest7 Guest8 to the text box. But ofcourse you can change the output to be anywhere you want. 

Option Explicit

Dim allHosts As Range
Private pHostRow As Integer
Dim hostColumn As Integer

Private Sub UserForm_Initialize()

    Dim Host As Range

    Dim firstHost As Range
    Dim lastHost As Range

    Dim lastHostRow As Integer

    hostColumn = 1

    Set firstHost = Cells(1, hostColumn)
    lastHostRow = firstHost.End(xlDown).Row

    Set lastHost = Cells(lastHostRow, hostColumn)
    Set allHosts = Range(firstHost, lastHost)

    For Each Host In allHosts
        ComboBox1.AddItem Host.Text
    Next Host

End Sub

.

Private Sub ComboBox1_Change()

    Dim selectedHost As String
    selectedHost = ComboBox1.Text

    pHostRow = allHosts.Find(selectedHost).Row

    Dim guest As Range
    Dim allGuests As Range
    Dim firstGuest As Range
    Dim lastGuest As Range
    Dim lastGuestCol As Integer

    Dim Host As Range
    Set Host = Cells(pHostRow, hostColumn)
    lastGuestCol = Host.End(xlToRight).Column

    Set firstGuest = Host.Offset(0, 1)
    Set lastGuest = Cells(pHostRow, lastGuestCol)
    Set allGuests = Range(firstGuest, lastGuest)

    TextBox1.Text = selectedHost

    For Each guest In allGuests
        TextBox1.Text = TextBox1.Text & selectedHost & guest.Text & vbCrLf
         'if you weren't outputting this to a textbox you wouldn't use the vbCrLf,
         'instead you would iterate to the next line in your output range.
    Next guest

End Sub

You can see how you can modify this so you would iterate through all the hosts (i.e. while populating the combobox) and for each host call the ComboBox1_Change() event (renamed of course and made a regular sub) to output all the guests to some range that you are iterating through on another worksheet.

Hope it helps!

Upvotes: 0

iDevlop
iDevlop

Reputation: 25262

The way for double lookups is to use INDEX(MATCH(...), MATCH(...)). in Excel 2003 you can even activate the Lookup Wizard that does it for you.

Upvotes: 0

Related Questions