Match function

[lvsmr2]

Hi everyone,

I need some help with the match function. I have attached a spreadsheet with has the following:

1. on the left hand side is the data we have collected
2. on the right hand side is where we need the match data to work ie under the column F with a1, we need the have all the data from the left hand side that matches a1 to be listed here, etc for all the other columns after. Hope that makes sense. If you need to ask me any more detail, just let me know. Thanks in advance for your help.

[windknife]

Try this,

F3

=IF(ROWS($A$2:A2)<=COUNTIF(Sheet1!$B$2:$B$100,F$2),INDEX($A:$A,SMALL(IF($B$2:$B$100=F$2,ROW($A$2:$A$100),""),ROWS($A$2:A2))),"")

committed with Ctrl+Shift+Enter, then copied down.

You can also see attached.
Hope this helps,
windknife

[lvsmr2]

Hi,

Thanks heaps for your help, works great. Greatly appreciated.

Cheers

[pike]

another option

Option Explicit
Sub ptest()
    Dim found  As Range, LookFor$, fAddress$, i!, ii!
    [F3:V27].ClearContents
    [A1].Activate
    With Worksheets(1).Range("B1", Range("B" & Rows.Count).End(xlUp))
        For ii = 0 To 16 Step 2
            LookFor = [F2].Offset(0, ii)
            Set found = .Find(LookFor, LookIn:=xlValues)
            i = 1
            If Not found Is Nothing Then
                fAddress = found.Address
                Do
                    [F2].Offset(i, ii + 0) = found.Offset(0, -1)
                    i = i + 1
                    Set found = .FindNext(found)
                Loop While Not found Is Nothing And found.Address <> fAddress
            End If
        Next ii
    End With
End Sub

but using excel functions where possible is allways the first choice