Find and Match Formula

[BlueStone]

Hi Experts,

I've attached a sample record that contains 2 worksheets (StudentRecord_Sample.xls).

Sheet1 contains student record information such as: Index, Group Name, Student ID, Activities
Sheet2 contains Student ID, Index

Sheet1 is a raw data. By only using the given student ID in Sheet2 (column A), how would it be possible to extract the "INDEX" information and populate its information as shown in colum B (yellow highlight)?

Apprecite for your kind help.

Thank you.

- Blue

[BlueStone]

Hi Experts,

Anybody can help me please?

Thanks.


- Blue

[Daxton A.]

I'm confused about what is you're looking for. Can you further explain please.

[Daxton A.]

I gotta run, sry!

[BlueStone]

Hi Daxton,

Thanks for your reponse.

If you open up the attached excel file, you can see that "Sheet1" contains raw data such as: Index, Group Name, Student ID, Activities. Sheet2 contains only 2 column: Student ID and Index.

The intention is to use the list of Student ID in "Sheet2" which are randomly chosen (fixed value) to match one by one these Student ID in "Sheet1" and return the finding in column Index (Sheet2).

Hope this explain better.

Thanks.


- Blue

[BlueStone]

Hi Experts,

Anybody else can help me please?

Thanks.

- Blue

[BlueStone]

Hi Experts,

Anybody?

- Blue

[Simon Lloyd]

There's a really good explanation of index match here http://fiveminutelessons.com/learn-microsoft-excel/use-index-lookup-multiple-values-list

[mancubus]

hi.

vba solution:



Sub return_indexes_given()


Dim ws1 As Worksheet, ws2 As Worksheet
Dim i As Long, j As Long
Dim indexes As String

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

With ws2
For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
indexes = ""
For j = 2 To ws1.Cells(Rows.Count, "C").End(xlUp).Row
If InStr(1, ws1.Cells(j, "C"), .Cells(i, "A")) > 0 Then indexes = indexes & ", " & ws1.Cells(j, "A")
Next
.Cells(i, "B") = Mid(indexes, 3)
Next
End With


End Sub

[Bob Phillips]

A UDF


Public Function Matches(LookIn As Range, Lookup As Range, LookFor As Range) As String
Dim ary

ary = Application.Transpose(Application.Evaluate("IF(ISNUMBER(SEARCH(" & Lookup.Address & "," & LookIn.Address(, , , True) & "))," & LookFor.Address(, , , True) & ")"))
Matches = Replace(Replace(Join(ary, ","), "False,", ""), ",False", "")
End Function


use like so

=MATCHES(Sheet1!$C$2:$C$11,A2,Sheet1!$A$2:$A$11)

[BlueStone]

Hi mancubus,

Thanks a lot for your response.

It works great, appreciate for your help.


- Blue

[BlueStone]

Hi xld,

Thanks for your response.

I could not use your method, well, maybe I don't know how to use it.

Could you provide some instructions on how to use it?


- Blue

[mancubus]

Hi mancubus,

Thanks a lot for your response.

It works great, appreciate for your help.


- Blue

you are welcome.

[mancubus]

Hi xld,

Thanks for your response.

I could not use your method, well, maybe I don't know how to use it.

Could you provide some instructions on how to use it?


- Blue

copy the udf in a standard module.

copy the formula =MATCHES(Sheet1!$C$2:$C$500,A2,Sheet1!$A$2:$A$500) to cell B2 in Sheet2. then copy B2 down to desired cell.

change the bottom row's number in range references to suit. i changed 11's to 500's for instance.

[BlueStone]

Hi mancubus,

Once again, thanks a lot for your response and help.

Managed to get it working now.

Have nice day.


- Blue

[mancubus]

thanks for the feedback.

pls mark the thread as solved from "thread tools" dropdown ( which is above the first post).