Find the position of a cell by sub-string based on another range in an array (Google Sheets formula)

Question

I need a specific formula that would give me the position of a cell in a column (Data!A:A) based on values form another range (A:A). Those values are strings of numbers that can be found as sub-strings of the values in Data!A:A.

I was able to successfully make a formula that does this for a single row, but I need one that would go through the whole list. What I made was:

=LET(data,Data!$A:$A,
Match(Filter(data,ISNUMBER(Search(A1,data))),data,0))

But when I try to simply wrap in an arrayformula it it gives me wrong answers

=LET(data,Data!$A:$A,
ARRAYFORMULA(IF(A:A="",,
Match(Filter(data,ISNUMBER(Search(A1:A,data))),data,0))))

I made and example sheet to visualize it - https://docs.google.com/spreadsheets/d/1-R-zWDZKarLAAYg4c5drJUolLFII0vSkt5nIuEHsWIA/edit?usp=sharing

Does anyone have an idea how to make it work?

Please do not present new requirements after you have received an answer. Ask only one question per post. Post a new question instead. — doubleunary
– doubleunary, Commented Aug 16, 2024 at 13:53

rockinfreakshow · Accepted Answer · 2024-08-16 14:38:22Z

0

You may try:

=map(A:A,lambda(Σ,if(Σ="",,index(ifna(xmatch("*"&Σ&"*",to_text(Data!A:A),2),"-")))))

UPDATED:

=map(A:A,lambda(Σ,if(Σ="",,index(index(split(Data!A:A,": ",),,2),xmatch("*"&Σ&"*",to_text(Data!A:A),2)+5))))

UPDATE 2:

= map(A:A,lambda(Σ,if(Σ="",,index(1*(REGEXEXTRACT(Data!A:A,"[\d.-]+")),xmatch("*"&Σ&"*",to_text(Data!A:A),2)+5))))

edited Aug 16, 2024 at 14:38

answered Aug 16, 2024 at 12:13

rockinfreakshow

30.7k4 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Mikołaj Gano Over a year ago

Ah, I knew there's gonna be a lambda there 😅 Those are still a little bit over my head, but thank you - it works for the example I provided. Unfortunately I don't understand how your answer works and the position of the row is only a part of what I actually need. I didn't want to overcomplicate the question as I had the other part figured out somewhat, but it doesn't work with the construction you gave me. I will edit the post with more details, if you would like to help me further.

rockinfreakshow Over a year ago

Added updated formula...

Mikołaj Gano Over a year ago

It works, thanks a ton! I have only a slight problem in that I the formula picks up commas that appear in the data set. I forgot to up them in the example, sorry about that. The output has to be a number.

rockinfreakshow Over a year ago

replace the index(split(Data!A:A,": ",),,2) part with your working regexextract model

Mikołaj Gano Over a year ago

I'm sorry but that doesn't work. That regexextract relayed on finding the row with my method first and that is ineffective. Placing that whole bit into your lambda function doesn't make it work. I get #N/A - no matches found in filter.

|

Collectives™ on Stack Overflow

Find the position of a cell by sub-string based on another range in an array (Google Sheets formula)

1 Answer 1

UPDATED:

UPDATE 2:

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATED:

UPDATE 2:

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related