CODEWITHSUNDEEP
excelmatchoffset
Debbie Oomen
Debbie Oomen

Reputation: 333

Find all matches between two columns and return values from another column horizontally

I am trying to figure out the most efficient way to find all matches of multiple values in one column and return values from another column. Let me try to explain it with images below:

Data containing the values to match

Here I am trying to find all matches of C2 and C3 (654001T0 and 654001T1) in a table on a different sheet. This is my sheet with the table:

Data where to find the matches and values to return

In column B is where I need to match C2 and C3 from the first sheet. When the matches are found, I need to return the values from column H in a horizontal way.

This is the desired outcome: Outcome

I am trying a combination of `IFERROR, INDEX, OFFSET, MATCH but I can't seem to get it to work for more than just the first match.

Upvotes: 0

Views: 33

Answers (1)

rotabor
rotabor

Reputation: 4698

This is solution for Microsoft 365:

enter image description here

[Sheet1!D2 and fill down]=TOROW(FILTER(Sheet2!$H$4:$H$9;Sheet2!$B$4:$B$9=C2))

Assuming the first picture is Sheet1, the second - Sheet2.

For earlier versions of Excel use the VBA solution:

Option Explicit

Sub Solution()
  Dim cc As Range, i&, j&, arB, arH, probe As String
  arB = Range(Sheet2.[B4], Sheet2.[B4].End(xlDown))
  arH = Range(Sheet2.[H4], Sheet2.[H4].End(xlDown))
  For Each cc In Range(Sheet1.[C2], Sheet1.[C2].End(xlDown)).Cells
    i = 1
    probe = cc
    For j = 1 To UBound(arB)
      If arB(j, 1) = cc Then
        cc.Offset(0, i) = arH(j, 1)
        i = i + 1
      End If
    Next
  Next
End Sub

Upvotes: 1

Related Questions