Vlookup to return column header name

[rodge]

Hi,

I'm currently having a very hardtime with the problem stated on the title.
Student # Math Science Physics Health History
123 Passed Passed Passed
456 Passed Passed Passed Passed Passed
789 Passed Passed
987 Passed Passed Passed Passed
654 Passed Passed Passed
321 Passed Passed Passed

I need to look up the student number and will return all the subject passed separated by comma in one cell.

Please help.

Thanks in advance!!

[galvinpaddy]

Sample worksheet might help make it easier to understand the problem and therefore the solution
galvinpaddy

[Cutter]

Something like this:

Please Login or Register  to view this content.

This covers the first 3 subjects so add the last 2


Edit: the above formula assumes your sample data is in range A1:F7 and the student # you want to get the result for is in H3

[rodge]

Hi,

Attached is what I got. I need to look up for employee ID and will return all applications on the column header that has "OK" value in row in one cell separated by comma.

Employee ID AIQUIC AIWCS Prod CAPRI CRUIS DBS DCRS DMS Eexcess EMIS PROD ePRS Prod eStart Prod eUPS Prod EWC EZ Audit iView iworks Prod
5200309 OK OK OK
5200306 OK OK OK
5200307 OK OK OK
5200305 OK OK OK
5200257 OK
Hope you can help me with this.

Thank you so much in advance.

[Cutter]

What happened to your original problem?

[rodge]

Its the same problem, it's just that instead of subjects, applications are need to be looked up and "OK" is the row value. I have like 80 applications and when I look up the employee number all applications that he has (valued "OK" on rows) will be all shown separated by a comma.

Hope you can help me with this. It's been 2 weeks and still I can't solve the puzzle.

thanks so much!!

[rodge]

Hi Forum Guru,

The above formula you gave works, however, it has limits look up value. The oter results are already inacurate.

Thanks hope you can help me.

[Cutter]

If you submitted your original problem as a simplified sample of your actual problem you should have indicated that because I'm afraid you over-simplified it. Your original post indicated only 5 headings so the solution I suggested would work (even though the formula I provided was lengthy). Now your actual problem has over 60 headings so the formula is no longer of any value. I think you're going to have to go with a VBA solution and, although I could hack some code together that would achieve the desired result, I think it's best to wait for a VBA expert to respond.

Edit: I have requested to have someone with VBA skills to have a look at this thread.

[JBeaucaire]

A worksheet change event can lookup the code selected and bring over all the matching results. I've added this code to your workbook. I've created a named range called MyIDs on the Data sheet consisting of all the values in column A, just add to the list and the ID selection drop box on the LOOKUP sheet will include all the IDs you have.

Your results will appear in C2 as you select in B2.

Please Login or Register  to view this content.

[martindwilson]

this will give up to 16 results

[rodge]

Hi,

Thank you so much for all the replies.. I really appreciate it.

Sorry but I'm getting more confuse with all the formulas - so complex for me.

I have attached the file that I am working with. Hope you can put the formala on where the comment goes "I need all applications to be present here".

Thanks so much in advance!!

-- Rodge

[JBeaucaire]

you had changed the name of the worksheet_change macro so it wouldn't work. I changed it back and added some simpler formulas into the table to make the REF01 sheet unnecessary. Now you can use the drop down in C4.

[rodge]

Hi JBeaucaire,

Thank you so much!! It works this time.. you saved me from getting fired!!

[JBeaucaire]

Well that's good to hear.


If that takes care of your need, please click Thread Tools above your first post and mark this thread as SOLVED.

[rodge]

Hi,

May I know whats wrong with this: it's no longer displaying the desired results.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, cFND As Range, c As Range, buf As String

Application.EnableEvents = False
If Len([C4]) > 0 Then
Set cFND = Sheets("Raw Data").Range("$A$2:$A$700").Find([C4].Value, LookIn:=xlValues, LookAt:=xlWhole).EntireRow.SpecialCells(xlConstants)
For Each c In cFND
If c.Value = "OK" Then buf = buf & ", " & Sheets("Raw Data").Cells(1, c.Column).Value
Next c
End If
If Len(buf) > 0 Then [C17].Value = Mid(buf, 2, Len(buf)) Else [C17] = ""
Application.EnableEvents = True

End Sub


The formula is based from the attached file on previous comments.

thanks for your help in advance.

[JBeaucaire]

What is your question? Clearly it WAS working, so you'll have to present actual supporting documentation if you want troubleshooting on your workbook (probably changed in some undescribed way.)