Vlookup: carriage return in Lookup value and output?

[Zexall]

Hi All - Hoping a ninja can help with a means to make this work.

I have a sheet where sometimes the values are a one to one match, and sometimes there are multiple inputs that require multiple matches (2 at most). Those with multiple, are separated with a carriage return, and ideally I'd like the output to have a corresponding carriage return. I've attached a small example with a expected output. Any ideas would be greatly appreciated.

Thanks much!

 

[AlanSidman]

Using Power Query, I reorganized your Lookup Field. Here is the Mcode and what the result looks like. With data like this, you can now do a Vlookup

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitColumnWithLineFeed = Table.SplitColumn(Source, "Look_up", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Look_up.1", "Look_up.2"}),
    UnpivotColumns = Table.UnpivotOtherColumns(SplitColumnWithLineFeed, {}, "Attribute", "Value"),
    RemoveColumnNotNeeded = Table.RemoveColumns(UnpivotColumns,{"Attribute"})
in
    RemoveColumnNotNeeded

v	A
1	Value
2	3500074170
3	3500074150
4	3610049180
5	3500077012
6	3610020225
7	3500077012

 

[Zexall]

Unfortunately that wouldn't work - I need to keep them grouped together in the end. I thought about using power query to split it into columns (so I knew what exactly was grouped together at the start), do searches, then concat it back, but I'd prefer to not do that if I don't have to.

 

[Marc L]

Beginner level formula to enter in cell B2 :​

​

=IF(ISERR(SEARCH(CHAR(10),A2)),VLOOKUP(A2,D:E,2,FALSE),VLOOKUP(LEFT(A2,SEARCH(CHAR(10),A2)-1)*1,D:E,2,FALSE)&CHAR(10)&VLOOKUP(MID(A2,SEARCH(CHAR(10),A2)+1,LEN(A2)-SEARCH(CHAR(10),A2))*1,D:E,2,FALSE))​

​

The Wrap Text check box must be checked within the cell formatting Alignment tab (first check box of Text control section) …​

​

Do you like it ? So thanks to click on bottom right Like !​

​

 

[AlanSidman]

I need to keep them grouped together in the end.

But in your file which showed your expected solution, it looks exactly like I have presented. I don't understand why you cannot use this as a step to your final solution. You have confused me greatly.

 

[Marc L]

No Alan as the expected result is the column B as it is from column A as it is …​

​

 

[bosco_yip]

In the Column A criteria range, cell A4 and A5 have multiple value and with duplicate value (3500077012)

VLOOKUP() unable to return the 2nd match value, and should be replaced with INDEX+AGGREGATE()

Thus,

In B2, formula copied down, and formatted with "Wrap Text" :

=IF(A2="","",IF(ISERR(FIND(CHAR(10),A2)),VLOOKUP(A2,D$2:E$7,2,0),VLOOKUP(0+LEFT(A2,10),D$2:E$7,2,0)&CHAR(10)&INDEX(E$1:E$7,AGGREGATE(15,6,ROW(D$1:D$7)/(D$1:D$7=0+RIGHT(A2,10)),SUMPRODUCT(1-ISERR(FIND(RIGHT(A2,10),A$2:A2)))))))

 

[shrivallabha]

If you have TEXTJOIN function then a simpler formula can be used.

=TEXTJOIN(CHAR(10),TRUE,IF(ISNUMBER(SEARCH($E$2:$E$7,A2,1)),$F$2:$F$7,""))