Match Text from one sheet to another and return text

[jbaergen]

Hello - can someone help me with the attached sample file?

I need to take the name of a school in (sheet 1,column E), search a list of school names on (sheet 2,Column A) to find that name, and return the corresponding cohort name from (sheet 2,column B) to (sheet 1,column F).
I've included 6 examples, but our raw data sheet has 271,509 entries (student info attached to school info).

Thanks!
jb

[Bishonen]

shall all names be searched or only a selected one?

[jbaergen]

All names.

[K m]

Try this in cell F2

=INDEX(Cohorts!A:B,MATCH(E2,Cohorts!A:A),2)

Copy formula down

[Bishonen]

Sub test()

    Dim rng As Range, cell As Range, i As Integer, nname As String
    application.screenupdating = false
    Sheets(1).Activate
    For Each cell In Range("E2", Cells(Rows.Count, 5).End(xlUp))
    
        nname = cell.Value
        Sheets(2).Activate
        Set rng = Cells.Find(nname, after:=Cells(2, 2), searchorder:=xlByRows, lookat:=xlWhole)
        If Not rng Is Nothing Then
            rng.Offset(0, 1).Copy
            Sheets(1).Activate
            cell.Offset(0, 1).PasteSpecial xlPasteValues
        End If
    Next cell

application.screenupdating = true
End Sub

[FDibbins]

=vlookup(E2,Cohorts!$A$2:$A$6),2,false)

[FDibbins]

so make up a table with all names, and then apply that formula

[Kumail Rizvi]

Hello!
How can I count specific word in different rows and in one column

Thanks
SMKR