Index match for text in a box

Question

I have a large dataset that contains several fields that I need to derive data from. However, the data is correlated arrays that have plenty of irregularity. I often need to split the data, find the item in one cell, and then grab the correlating item from another cell in the same position in the array.

Screenshot of the cells:

Text version

A1:
100001

B1:
0123456789

A2:
100100
100001
100600

B2:
54321
0123456790
321321321321321

A3:
100100
100600
100001

B3:
54321
987987987987987
0123456791

A4:
100600
100100
100001

B4:
654654654654654
54321
0123456792

A5:
100001
100600
100100

B5:
0123456793
456456456456456
54321

The correct solution will Find the item in Column B on the same line as the item in Column A that lists "100001"

Credit to [excelisfun] and his excel magic tricks for this solution that works without code.

I can stack some formulas that totally work:

=IFERROR(MID(B1,FIND("~",SUBSTITUTE(B1,CHAR(10),"~",FIND("100001", A1)-FIND("100001",SUBSTITUTE(A1,CHAR(10),""))))+1,10),IF(A1="100001", B1, IF(LEFT(A1,6) ="100001",LEFT(B1,10),"")))

Basically, you use len and len + substitute to count the number of separators. Then you leverage substitute's instance_num to find the item in column B. Also, you have to account for first item, only item, and not there.

I had already implemented code, so I was not averse to a code solution, and I am really tired of freehanding this formula.

ghangas ghangas · Accepted Answer · 2018-03-29T18:45:25

In code it was simple enough to split the contents of a cell and then use the built-in index and match functions from Excel: I popped this into my personal.xlsx file

Public Function CellMatch(find_text, within_text, delimiter)
    arrLookup = Split(within_text, delimiter)
    CellMatch = Application.Match(find_text, arrLookup, 0)
End Function

Public Function CellIndex(cell_array, number, delimiter)
    arrLookup = Split(cell_array, delimiter)
    CellIndex = arrLookup(number - 1)
End Function

Then I use it like the standard index match combo

=CellIndex(B1,CellMatch("100001",A1,CHAR(10)),CHAR(10))

Side effect, if you stack it you can parse HL7 super easy as well.

A6: MFI|PRA^PRACTITIONER MASTER FILE^SOURC|SOURC^DEST^L|UPD|20180312074145||NE

C6:

=CellIndex(CellIndex(A6,2,"|"),2,"^")

result: PRACTITIONER MASTER FILE

Index match for text in a box

1 Answers