Help with a lookup based on multiple values in a single cell

[will0r]

Hi everyone,

I'm not exactly sure how to even ask this properly but hopefully my image and little explanation will be enough to get started!

What I'm trying to achieve is to take the values from Table 1 (with Column A) and perform a lookup into Table 2 (via Column D) and return the full-description (from Column E) back to Table 1 (place them all in Column B).

Hopefully there's a suitable solution for this as I think, well actually, I know I am struggling with this one!

Appreciate all the help in advance. Thanks!

 

[Eric W]

Welcome to the MrExcel forum!

Are all those codes in column A? And you're looking to find the matching code for all of them? If so, try:

Book1
	A	B	C	D	E
1	Notification Status	Full Status		Stat	Status
2	NOPR NOTI NOPT INIT	Initialized, Not prompt, Not initialized, No parts		1100	Can be archived
3	NOPR NOTI INIT DSGN QLTY MFG	Initialized, Designed, Manufactured, Not prompt, Not initialized, Quality department advised		AALK	Account assignment locked
4	ABCD NOPR INIT	code ABCD, Initialized, Not prompt		ABCD	code ABCD
5	AB INIT	code AB, Initialized		AB	code AB
6	EFGH ABCD AB	code ABCD, code AB, code EFGH		EFGH	code EFGH
7	AB UNK XYZ INIT	code AB, Initialized		INIT	Initialized
8	UNK XYZ			DSGN	Designed
9				MFG	Manufactured
10				NOPR	Not prompt
11				NOTI	Not initialized
12				NOPT	No parts
13				QLTY	Quality department advised
14
15
Sheet6

Cell Formulas
Range	Formula
B2:B10	B2	=TEXTJOIN(", ",1,IF(ISNUMBER(SEARCH(" "&$D$2:$D$20&" "," "&A2&" "))*(A2<>""),$E$2:$E$20,""))

TEXTJOIN was added to Excel in the 2019 version, so this only works in 2019 or newer. A few things to note, the Full Status will be in the order of the table, not necessarily the order of the entry in column A. Also, if there is an unknown code (A7 and A8), there is no indication.


FYI, there's a tool here called XL2BB. It makes it much easier to share your sheet to make it easier for helpers to look at your data and copy it without having to retype everything. Check out the link in the reply box or my signature. It's easy to download, install, and use.

 

[will0r]

Welcome to the MrExcel forum!

Are all those codes in column A? And you're looking to find the matching code for all of them? If so, try:

Book1
	A	B	C	D	E
1	Notification Status	Full Status		Stat	Status
2	NOPR NOTI NOPT INIT	Initialized, Not prompt, Not initialized, No parts		1100	Can be archived
3	NOPR NOTI INIT DSGN QLTY MFG	Initialized, Designed, Manufactured, Not prompt, Not initialized, Quality department advised		AALK	Account assignment locked
4	ABCD NOPR INIT	code ABCD, Initialized, Not prompt		ABCD	code ABCD
5	AB INIT	code AB, Initialized		AB	code AB
6	EFGH ABCD AB	code ABCD, code AB, code EFGH		EFGH	code EFGH
7	AB UNK XYZ INIT	code AB, Initialized		INIT	Initialized
8	UNK XYZ			DSGN	Designed
9				MFG	Manufactured
10				NOPR	Not prompt
11				NOTI	Not initialized
12				NOPT	No parts
13				QLTY	Quality department advised
14
15
Sheet6

Cell Formulas
Range	Formula
B2:B10	B2	=TEXTJOIN(", ",1,IF(ISNUMBER(SEARCH(" "&$D$2:$D$20&" "," "&A2&" "))*(A2<>""),$E$2:$E$20,""))

TEXTJOIN was added to Excel in the 2019 version, so this only works in 2019 or newer. A few things to note, the Full Status will be in the order of the table, not necessarily the order of the entry in column A. Also, if there is an unknown code (A7 and A8), there is no indication.


FYI, there's a tool here called XL2BB. It makes it much easier to share your sheet to make it easier for helpers to look at your data and copy it without having to retype everything. Check out the link in the reply box or my signature. It's easy to download, install, and use.

Eric, thank you greatly for your help on this! Out of curiosity, is there a way I can allow for Full Status to ordered as per Status in Column A? If there's a bit of prep work involved, that's fine too!

Cheers!

 

[Eric W]

I don't quite understand what you want.

 

[Marcelo Branco]

If a UDF (User Defined Function) is acceptable, try

Paste the code below In a Standard Module

Function FullStatus(s As String, r As Range) As String
    Dim spl As Variant, rFound As Range, sAux As String
    Dim i As Long
  
    spl = Split(Application.Trim(s), " ")
    For i = LBound(spl) To UBound(spl)
        Set rFound = r.Find(what:=spl(i), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not rFound Is Nothing Then sAux = sAux & rFound.Offset(, 1) & ", "
    Next i
    If Len(sAux) > 0 Then FullStatus = Left(sAux, Len(sAux) - 2)
End Function

30062021 Testes.xlsm
	A	B	C	D	E
1	Notification Status	Full Status		Stat	Status
2	NOPR NOTI NOPT INIT	Not prompt, Not initialized, No parts, Initialized		1100	Can be archived
3	NOPR NOTI INIT DSGN QLTY MFG	Not prompt, Not initialized, Initialized, Designed, Quality department advised, Manufactured		AALK	Account assignment locked
4	ABCD NOPR INIT	code ABCD, Not prompt, Initialized		ABCD	code ABCD
5	AB INIT	code AB, Initialized		AB	code AB
6	EFGH ABCD AB	code EFGH, code ABCD, code AB		EFGH	code EFGH
7	AB UNK XYZ INIT	code AB, Initialized		INIT	Initialized
8	UNK XYZ			DSGN	Designed
9	DSGN	Designed		MFG	Manufactured
10				NOPR	Not prompt
11				NOTI	Not initialized
12				NOPT	No parts
13				QLTY	Quality department advised
Plan4

Cell Formulas
Range	Formula
B2:B9	B2	=FullStatus(A2,D$2:D$13)

Save the file as .xlsm

Hope this helps

M.

 

[Eric W]

How about:

Book1
	A	B	C	D	E
1	Notification Status	Full Status		Stat	Status
2	NOPR NOTI NOPT INIT	Not prompt, Not initialized, No parts, Initialized		1100	Can be archived
3	NOPR NOTI INIT DSGN QLTY MFG	Not prompt, Not initialized, Initialized, Designed, Quality department advised, Manufactured		AALK	Account assignment locked
4	ABCD NOPR INIT	code ABCD, Not prompt, Initialized		ABCD	code ABCD
5	AB INIT	code AB, Initialized		AB	code AB
6	EFGH ABCD AB	code EFGH, code ABCD, code AB		EFGH	code EFGH
7	AB UNK XYZ INIT	code AB, Initialized		INIT	Initialized
8	UNK XYZ			DSGN	Designed
9				MFG	Manufactured
10				NOPR	Not prompt
11				NOTI	Not initialized
12				NOPT	No parts
13				QLTY	Quality department advised
14
Sheet6

Cell Formulas
Range	Formula
B2:B10	B2	=TEXTJOIN(", ",1,IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),SEQUENCE(100,,1,100),100)),$D$2:$E$20,2,0),""))

But this version only works in Excel 365.