What function or VBA can I use with Line Breaks in Cell

[ithinkpro]

Hello, I have tried using Index Match and Vlookup but keep getting no results. I even format to wrap and set all data to match format.

I would like something similar to the screenshot below.

I need a function or VBA that would pull from a cell with line breaks and output with line breaks.


Example: Rule AA (F2) will lookup table A:B, and will match AA (F2) with Column B and provide me with Values that match which will be A and B.

Im using Excel 2013

If you need more clarification, please let me know.

Thank you

 

[Peter_SSs]

Welcome to the MrExcel board!

You could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down. You will need to set the formula column to 'Wrap Text' & possible adjust row heights.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function WQ(rData As Range, sRule As String) As String
  WQ = Join(Filter(Split(Join(Filter(Split(Join(Application.Transpose(Evaluate(rData.Columns(1).Address & "&""@""&char(10)&" & _
        rData.Columns(2).Address)), vbLf & "|") & vbLf, "|"), vbLf & sRule & vbLf), "@"), "@"), vbLf, False), vbLf)
End Function

ithinkpro.xlsm
	A	B	C	D	E	F	G
1	WQ	Rule				Rule	WQ
2	A	AA AB AC				AA	A B
3	B	AA AB				AB	A B C
4	C	AB AC				AC	A C
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=WQ(A$2:B$4,F2)