I have the following set of data. In sheet A the data appears like "John D #23". In sheet B the data appears like "J Doe #23 (other info no numbers)".

I need to write an excel code for sheet B that finds "23" (or whatever number is in the cell). I need that number to be compared to Sheet1!A2:A, find the cell where "23" occurs and return all data left of "#" trimmed.

In a perfect world this is an arrayformula. Is this even possible?

I have the following set of data. In sheet A the data appears like "John D #23". In sheet B the data appears like "J Doe #23 (other info no numbers)".

I need to write an excel code for sheet B that finds "23" (or whatever number is in the cell). I need that number to be compared to Sheet1!A2:A, find the cell where "23" occurs and return all data left of "#" trimmed.

In a perfect world this is an arrayformula. Is this even possible?

• excel
• indexing
• match
• trim

• 3 It is possible, but there are many conditions to check so it will be a long formula. What happens if there are multiple matches? what happens for cases where there is a space between # and the first digit?, when there are multiple # chars. The exact version of EXCEL is also important (are regular expressions functions like REGEXEXTRACT available?) – tinazmu Commented Mar 26 at 23:00
• 2 Sheet1!A2:A is valid syntax only in Google sheets (as is ArrayFormula()) - you should adjust your tags accordingly. – Spectral Instance Commented Mar 26 at 23:18
• Here's what I was able to come up with... it's close but I think it's matching the first digit of a two digit number. =TRIM(LEFT(INDEX(S_National!$A$20:$A$31, MATCH(TRUE, ISNUMBER(SEARCH("#"&TRIM(MID(A2,FIND("#",A2)+1,FIND("(",A2)-FIND("#",A2)-1)), S_National!$A$20:$A$31)), 0)), SEARCH(" #", INDEX(S_National!$A$20:$A$31, MATCH(TRUE, ISNUMBER(SEARCH("#"&TRIM(MID(A2,FIND("#",A2)+1,FIND("(",A2)-FIND("#",A2)-1)), S_National!$A$20:$A$31)), 0))) - 1)) – Dave Commented Mar 26 at 23:36
• 1 @Dave Are you planning to respond to the questions and clarifications asked of you in the comments? – Ron Rosenfeld Commented Mar 27 at 0:21
• @tinazmu I am 100% confident that there will not be multiple matches and/or spaces between the # and first digit – Dave Commented Mar 27 at 13:56

2 Answers 2

I am not sure I fully understood which piece of information you want to find but this is the solution that I have found for what I think you're after

=INDEX(TRIM(LEFT(Sheet1!A2:A, FIND("#",Sheet1!A2:A)-1)), MATCH(TRIM(MID(I9, FIND("#", I9), FIND(" ", I9,FIND("#", I9))-FIND("#", I9))), RIGHT(Sheet1!A2:A, LEN(Sheet1!A2:A)-FIND("#", Sheet1!A2:A)+1), 0))

You did not include the cell address for the sheet B array so I have just used the cell reference I9, but this will work as an array, simply substitute it for the array on sheet B

I am not sure exactly what you want, as you provided no pictures, but you should be abel to adapt what I have provided.:

Note:
- this requires 365.
- I used LET and broke it up a bit to make the algorithm more clear.
- lookup range can be replaced by a table reference, or even a whole column reference if necessary, but the smaller the better.

=LET(
    lookup, SheetA!$A$2:$A$1000,
    a, REGEXEXTRACT(A2, "#\d+"),
    b, XMATCH(TRUE, ISNUMBER(FIND(a, lookup))),
    c, INDEX(lookup, b),
    TRIM(LEFT(c, FIND(a, c) - 1))
)

Sheet A Data

Sheet B Data and Results

If Sheet1 is a table, then you would enter the lookup formula in the first line of the table, and it will auto-populate the length of the table.