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

1
votes

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

2
stringgoogle-sheets
It looks like vlookup is what you need. It only works with one column, though: you need to know in which column the string is going to be. There is no built-in function for searching two-dimensional ranges. – user3717023

2 Answers

1
votes

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.

0
votes

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.