Is it even POSSIBLE to get multiple outcome via vlookup?

[sejun87]

Hello, I've got a question regarding extracting multiple values. I'm well aware of vlookup and other frequently used functions but I need your advice for this one.

[FONT=굴림][/FONT][FONT=맑은 고딕]Let's say there are teachers and we want to match their subjects they teach at school using pre-existing subject key codes. [/FONT]

[FONT=굴림][/FONT][FONT=맑은 고딕] [/FONT]

[FONT=굴림][/FONT][FONT=맑은 고딕]Let's say column E and F are already set, and I'm trying to fill in column C. Instead of putting typing every value, I'm using subject key codes. Teacher A and B are fine just using simple vlookup function, but when it comes to teacher C, who teaches both math and science, I don't seem to know any functions that would make this possible. [/FONT]

[FONT=굴림][/FONT][FONT=맑은 고딕] [/FONT]
[FONT=굴림][/FONT][FONT=맑은 고딕]Format doesn't matter (as shown in teacher D), but I'd like the result (cell C3) say Math, Science (or Math|Science). Is there any way I can possibly try? [/FONT]

[FONT=굴림][/FONT][FONT=맑은 고딕] [/FONT]
[FONT=굴림][/FONT][FONT=맑은 고딕]Thanks[/FONT]

[FONT=굴림][/FONT]

 

[Special-K99]

VBA's probably the easiest way to do this, but I'm no expert on VBA.

 

[Eric W]

There are some really long, complicated formulas to do what you want, but even then you'd need the TEXTJOIN function, which is currently only available in Excel 365. You're better off using a UDF. To try that, open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu select Insert > Module. In the window that opens, paste this code:

Public Function GetSubject(ByVal Codes As String, ByVal SubTab As Range, ByVal Delim As String) As String
Dim i As Long, MyVals As Variant
    
    For i = 1 To Len(Codes)
        If Not Mid(Codes, i, 1) Like "#" Then Mid(Codes, i, 1) = " "
    Next i
    MyVals = Split(WorksheetFunction.Trim(Codes), " ")
    On Error Resume Next
    For i = 0 To UBound(MyVals)
        GetSubject = GetSubject & Delim & WorksheetFunction.VLookup(CLng(MyVals(i)), SubTab, 2, 0)
    Next i
    GetSubject = Mid(GetSubject, Len(Delim) + 1)
    
End Function

Press Alt-Q to close the editor. Now you can use the new function as in the example below:

	A	B	C	D	E	F
Name	Subject key code	Subject	No.	Subject
Teacher A	Math	Math
Teacher B	Science	Science
Teacher C	Math, Science	English
Teacher D	1|2	Math, Science
Teacher E	2,4|3	Science, English

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1,2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=getsubject(B2,$E$2:$F$4,", ")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The first parameter is the key code, the next is the address of the table, and the last is the delimiter you want to use between the results.

 

[sejun87]

OMG

Eric, you the best.. I'm literally crying

 

[Eric W]

Aww!

I'm happy to have helped!