CODEWITHSUNDEEP
stringgoogle-sheets
Remy Konings
Remy Konings

Reputation: 35

Google Sheets - Search specific string in different tab and get value of cell on the right

I'm searching for a certain formula in Google Sheets. Let me explain the situation. I have a Google Sheets with multiple tabs. The values and strings within one of these tabs is changing each hour and the order is never the same.

So want is to search for a certain string in the tab that always changes. When that string is found, I would like to have the value of the cell on the right of that string.

The formulia would be something like this i guess: "tab,search of string,cell on the right"

Thanks, Remy

Upvotes: 1

Views: 1909

Answers (2)

JPV
JPV

Reputation: 27262

Alternatively,

This may also work (even for plural matches):

=concatenate(ArrayFormula(if(Sheet2!A:Z="String", offset(Sheet2!A:Z, 0, 1)&" ",)))

where Sheet2 is the tab that is searched and "String" is the search string.

Upvotes: 1

Wicket
Wicket

Reputation: 38200

The following will work if there is one and only one match. It's not case sensitive.

Assume that the tab to search is Sheet1. In another sheet add the following

Cell A1 : String to search
Cell A2 : Add the following formula

=ArrayFormula(
  INDIRECT("Sheet1!"&
   TRIM(
    JOIN("",
     QUERY(
      IF(ISERROR(SEARCH(A1,Sheet1!A1:C2)),
       ,
       ADDRESS(ROW(Sheet1!A1:C2),COLUMN(Sheet1!A1:C2)+1)
      ),
      ,
      1E+100
     )
    )
   )
  )
 )

Note: I only did the following tests:

Sheet1

+---+---+---+---+
|   | A | B | C |
+---+---+---+---+
| 1 | A | B | C |
| 2 | D | E | F |
+---+---+---+---+

Sheet2: Test 1

+---+------+
|   |  A   |
+---+------+
| 1 | E    |
| 2 | F    |
+---+------+

Sheet2 : Test 2

+---+------+
|   |  A   |
+---+------+
| 1 | a    |
| 2 | B    |
+---+------+

Note: If you require to make the above formula case sensitive, replace SEARCH by FIND.

Upvotes: 0

Related Questions