Lookup from comma separated values in columns and write maximum value

The input values are in column 1,2 & 3 and the output values are in column 4.

I want to Vlookup values in Column 3 (comma separated values of Pipe Numbers- column1) read these values and find the values from column 2 and write the maximum values for Maximum time of concentration.

For example, Pipe 4,

U/s connected pipes are 1,2,3 & 4 (column 3) Design time - column 4 will be maximum of Tc (column 2) of 1,2,3,4

CodePudding user response：

You may try these formulas below, provided Excel dependent

• Formula used in cell D2 --> Formula applicable in Excel 365 For Windows/MAC/Web

=MAX(XLOOKUP(0 TEXTSPLIT(C2,,",",1),$A$2:$A$7,$B$2:$B$7,B2))

• Formula used in cell E2

=MAX(IFERROR(VLOOKUP(FILTERXML("<m><b>"&SUBSTITUTE(C2,",","</b><b>")&"</b></m>","//b"),$A$2:$B$7,2,0),B2))

• Formula used in cell F2

=MAX(IFERROR(VLOOKUP(IFERROR(TRIM(MID(SUBSTITUTE(C2,",",REPT(" ",100)),ROW($1:$10)*100-99,100))*1,""),$A$2:$B$7,2,0),B2))

Note: Depending on one's Excel version this may needs to be keyed with CTRL SHIFT ENTER instead of Enter when exiting edit mode.