Finding values in multiple rows or columns, then returning the first value in the row or column

[seanbrown01]

I spent a day reading Excel Help articles and trying many different approaches to solving this, but have not been successful. If anyone can help me with the proper formula to make this happen, I would be most grateful. The goal is to identify which physical host (Hostname) a vm computer (vmname) resides on. Example images attached... I have a sizable spreadsheet that is not in a format that is "vlookup friendly". I've also tried to find a way to quickly change the data structure, to make it vlookup friendly, without success. Appreciate any help that can be provided. Thanks!

 

[kennypete]

A VBA rather than formula approach is:

Sub subVmnameToHostname()
    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    Dim rng As Range
    Set rng = Sheet2.UsedRange
    Dim r As Integer, c As Integer, i As String
    For r = 1 To rng.Rows.Count
        i = Sheet2.Cells(r, 1)
        For c = 2 To rng.Columns.Count
            If Len(Sheet2.Cells(r, c)) > 0 Then
                dic(Sheet2.Cells(r, c)) = i
            Else
                Exit For
            End If
        Next c
    Next r
    Sheet1.Activate
    Cells(1, 1) = "Vmname": Cells(1, 2) = "Hostname"
    Dim k
    c = 2
    For Each k In dic.keys
        Debug.Print k, dic(k)
        Cells(c, 1) = k: Cells(c, 2) = dic(k)
        c = c + 1
    Next
End Sub

My source data (NB: Sheet2):

Book1
	A	B	C	D	E	F	G
1	Hostname001	vmname001	vmname002	vmname003
2	Hostname002	vmname004
3	Hostname003	vmname005	vmname006	vmname007	vmname008	vmname009	vmname010
Sheet2

Output to (a blank) Sheet1:

Book1
	A	B
1	Vmname	Hostname
2	vmname001	Hostname001
3	vmname002	Hostname001
4	vmname003	Hostname001
5	vmname004	Hostname002
6	vmname005	Hostname003
7	vmname006	Hostname003
8	vmname007	Hostname003
9	vmname008	Hostname003
10	vmname009	Hostname003
11	vmname010	Hostname003
Sheet1

 

[sandy666]

with Power Query

HOST	1	2	3	4	5	6
Hostname001	vmname001	vmname002	vmname003
Hostname002	vmname004
Hostname003	vmname005	vmname006	vmname007	vmname008	vmname009	vmname010
HOST	VM
Hostname001	vmname001
Hostname001	vmname002
Hostname001	vmname003
Hostname002	vmname004
Hostname003	vmname005
Hostname003	vmname006
Hostname003	vmname007
Hostname003	vmname008
Hostname003	vmname009
Hostname003	vmname010

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.SelectColumns(Table.UnpivotOtherColumns(Source, {"HOST"}, "Attribute", "VM"),{"HOST", "VM"})
in
    Result

 

[Eric W]

Welcome to the MrExcel forum.

If you want a formula, consider this. With this lookup sheet:

Book2 (version 1).xlsb
	A	B	C	D	E	F	G
1	Hostname001	vmname001	xxx	yyy	zzz
2	Hostname002	aaa	bbb	ccc	vmname002
3	Hostname003	xxx	yyy	vmname003
4	Hostname004	rrr	sss	ttt	ttt	vvv	vmname004
Sheet5

Here are 2 formulas that work:

Book2 (version 1).xlsb
	A	B	C
1	Vmname	Hostname
2	vmname001	Hostname001	Hostname001
3	vmname002	Hostname002	Hostname002
4	vmname003	Hostname003	Hostname003
Sheet4

Cell Formulas
Range	Formula
B2:B4	B2	=INDEX(Sheet5!$A:$A,AGGREGATE(15,6,ROW(Sheet5!$B$1:$Z$10)/(Sheet5!$B$1:$Z$10=A2),1))
C2:C4	C2	=INDEX(Sheet5!A:A,AGGREGATE(15,6,ROW(Sheet5!$A$1:$A$4)/(COUNTIF(OFFSET(Sheet5!$B$1:$Z$1,ROW(Sheet5!$A$1:$A$4)-ROW(Sheet5!$A$1),0),A2)>0),1))

I provided 2 since these type of formulas are pretty computation intensive, especially with a large spreadsheet. If you try one, and your sheet slows down, try the other. If they are both slow, you may be best served by using one of the VBA or Power Query options.

 

[seanbrown01]

I cannot thank you enough, for such a quick reply. Hope you all have a great weekend!