help with index(match) function to lookup data

**arekkusu03** · 09-06-2012, 01:16 AM

Hey Guys

I was wondering if there was a formula in excel to find the Name (on "Output tab") and the letters (array =Data!C12:BB26)

"T" (Terminated)
"R" (Resigned)
"TR" (Transfer)
"X" (End Temp Assign)

and bring back the End of employment date Date, (array =Data!C11:BB11)

I've attached a sample below.

Sample2.xlsx

Ive attempted a basic index(match) formula but doesnt help me

Thank you

**benishiryo** · 09-06-2012, 02:10 AM

hi there again, arekkusu03. cant think of any formula, but here's a VBA code that might help:

Sub Test()

Rowx = 3
Do While Worksheets("Output").Cells(Rowx, 1) <> ""
Namex = Worksheets("Output").Cells(Rowx, 1)

Worksheets("Data").Select
Cells.Find(What:=Namex, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

NRow = Selection.Row

For Ncol = 3 To 52
    If Worksheets("Data").Cells(NRow, Ncol) = "T" Or Worksheets("Data").Cells(NRow, Ncol) = "R" _
    Or Worksheets("Data").Cells(NRow, Ncol) = "TR" Or Worksheets("Data").Cells(NRow, Ncol) = "X" Then
        Worksheets("Output").Cells(Rowx, "B") = Worksheets("Data").Cells(11, Ncol)
    End If
Next Ncol

Rowx = Rowx + 1
Loop

End Sub

**arekkusu03** · 09-06-2012, 02:15 AM

Hey benishiryo,

Thanks for your help again

Im sorry but how do you use VBA?? Im relatively new to excel, I've only started using last year for work related purposes.

So if you don't mind to give a brief tutorial that will definately help

Thank you.

**benishiryo** · 09-06-2012, 02:50 AM

you're welcome~

sure, you need to press ALT + F11 to get into the VBA editor first. go to Insert -> Module. you should see a blank page on the right. you can then paste the codes i've given over there. you can then close this window & return to Excel. to use the Macro, you can press ALT + F8 to choose which one to run. right now, it's probably just my maco. otherwise, you can also add a shape of any kind (circles, ovals, rectangles, textboxes) & right click the object -> Assign Macro. choose the macro i created and it's done.

hope that helps