0

I have an Excel table containing cable types and route numbers:

enter image description here

I need a formula that will search the route values at the column U for the value in AE14 cell and if match is found to add it's corresponding value to the cell and if more matches were found to add them to the cells on its right.

enter image description here

Right now I'm pretty close since my formula indeed finds the matches and displays them next to each other but I'm not searching the column U like i want but I split the digits from U to multiple columns and search those individually.

Is it possible to have the formula check for matches in V column where multiple values separated by ";" exist?

=IFERROR(INDEX($L$14:$L$86;AGGREGATE(15;3;($V$14:$V$86=$AE$14)/($V$14:$V$86=$AE$14)*(ROW($L$14:$L$86)-ROW($L$13));COLUMNS($Y$13:Y13)));"")

My Excel version is 2013.

Thank you in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vangde
  • 55
  • 8
  • Post data as text so that we can copy. What is your excel version? – Harun24hr Jun 24 '21 at 09:30
  • `=IFERROR(INDEX($L$14:$L$86;AGGREGATE(15;3;($V$14:$V$86=$AE$14)/($W$14:$W$86=$AE$14)*(ROW($L$14:$L$86)-ROW($L$13));COLUMNS($Y$13:Y13)));"")` Is that what you mean? I changed the 2nd `($V$14:$V$86=$AE$14)` to reference column W instead of V. Not tested it, but is that what you mean? – P.b Jun 24 '21 at 10:14
  • @P.b yes, but the problem is that contents of cells in V coloum have multple values separated by a semicolon. We need to account for that. – Vangde Jun 24 '21 at 10:20
  • So basically you want to get rid of helper columns V and W and check each individual separated the value of U in the current row to occur as separated value in other cells in column U? In that case, are separated values always 1 character (<10, and/or no digits)? – P.b Jun 24 '21 at 10:51
  • @P.b 23 correct – Vangde Jun 24 '21 at 11:15
  • @P.b could be only numeric values of length 1-3 digits seperated by ';" – Vangde Jun 24 '21 at 11:33
  • I only have a solution for 1 character: `=IFERROR(INDEX($A$1:$A$5;SMALL(IF((ISNUMBER(SEARCH(FILTERXML(""&SUBSTITUTE($B1;";";"")&"";"//s["&ROW(A$1:INDEX($A:$A;LEN($B1)-LEN(SUBSTITUTE($B1;";";""))+1))&"]");$B$1:$B$5))*(ROW($B$1:$B$5)<>ROW($B1)));ROW($A$1:$A$5));COLUMNS($A:A)));"")` [screen for used data ranges](https://i.stack.imgur.com/g4H2M.jpg) – P.b Jun 24 '21 at 12:36
  • @P.B Thank you very much for your sudgestion. You think that what I'm trying to acomplish is impossible(or very difficult) hence I should revize my approach or that this could be doable but with a newer version of Excel? – Vangde Jun 25 '21 at 09:02
  • I'd approach it differently. With a query you would be able to stretch your data into individual rows per separate value in column U. So 3 values will result in 3 rows containing all the current data, but individual value per row for column U. That will make a lookup/match way easier. Then office 365 would be ideally (FILTER), but a match/if/small combination would do in older versions. – P.b Jun 25 '21 at 10:32
  • 1
    Look at taylyn's answer here https://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal – P.b Jun 25 '21 at 16:09

0 Answers0