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

ithinkpro

New Member
Joined
Nov 29, 2021
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
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.

1638232308393.png


Im using Excel 2013

If you need more clarification, please let me know.

Thank you
 

Attachments

  • 1638232138187.png
    1638232138187.png
    5.4 KB · Views: 8

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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)

VBA Code:
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
ABCDEFG
1WQRuleRuleWQ
2AAA AB ACAAA B
3BAA ABABA B C
4CAB ACACA C
Sheet1
Cell Formulas
RangeFormula
G2:G4G2=WQ(A$2:B$4,F2)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top