CODEWITHSUNDEEP
google-sheets
Russell Tatum
Russell Tatum

Reputation: 11

Using parts of a string to search and find a cell reference

I've been toying with a few ways to do what I want to do, so I'll start with an example of my goal:

So, in the linked sheet, I have a table with colors and animals, and then a list of entries, each with a color and animal together. My goal is this: Take the string from one of the cells in column E, i.e. E2: Red Cat, and use that information to find the associated cell, and return either the cell reference B2, or the data contained where they intersect, in this case TRUE.

Upvotes: 1

Views: 151

Answers (3)

Max Makhrov
Max Makhrov

Reputation: 18707

Maybe a good way to automate your task is to use array formulas:

enter image description here

The formula is F1 to split values:

=FILTER(SPLIT(E2:E," "),E2:E<>"")

The formula in H2 to get addresses:

=FILTER(ADDRESS(MATCH(F2:F,A:A,),MATCH(G2:G,1:1,),4),E2:E<>"")

or put this in H2 to get values:

=FILTER(VLOOKUP(F2:F,A:C,MATCH(G2:G,1:1,),0),E2:E<>"")

enter image description here

Upvotes: 0

Grzegorz Mogilewski
Grzegorz Mogilewski

Reputation: 962

If you alter a little bit code given by @pnuts then you will get cell reference:

=ADDRESS(MATCH(INDEX(SPLIT(E2," "),1),A:A,0),MATCH(INDEX(SPLIT(E2," "),1,2),$1:1,0),4)

The last paramether could be 1-4 and the results:

  1. $B$2
  2. B$2
  3. $B2
  4. B2

also you are able to add sheet name to reference, then it would be:

=ADDRESS(MATCH(INDEX(SPLIT(E2," "),1),A:A,0),MATCH(INDEX(SPLIT(E2," "),1,2),$1:1,0),4,,"SheetName")

and the result -> SheetName!B2

Upvotes: 1

pnuts
pnuts

Reputation: 59475

I opted for the cell content rather than the cell addresses. Please try:

=OFFSET($A$1,MATCH(INDEX(split(E2," "),1),A:A,0)-1,MATCH(INDEX(split(E2," "),2),$1:$1,0)-1)

Upvotes: 1

Related Questions