I'm trying to lookup two sheets in Spreadsheet. One has the search value and other has all the data corresponding to the search string. I'm trying to find a formula that will search the value and return all the corresponding rows. Another thing is that it has to return the entire row and columns correspoing to that string and not only one.
I'm using this Vlookup formula as of now but any help will do:=
ArrayFormula(VLOOKUP(A1,Sheet2!A2:AE196,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24},FALSE))
A2:is the seach string, and used array to define the Columns to be returned. But the formula returns only one row corresponding to the string and not all the rows. Is there a formula that returns all rows and entire values in those rows correspoinding to the search string?
EX :Sheet1:search criteria
| Search |
|---|
| Apple |
| Mango |
| melon |
Ex:Sheet 2: Data
| Name | Colour | no |
|---|---|---|
| Apple | red | 5 |
| Banana | yellow | 3 |
| Apple | red | 25 |
| Mango | yellow | 1 |
| Mango | yellow | 10 |
Expected result for Apple(A1 in the formula) as search string:
| Name | Colour | no |
|---|---|---|
| Apple | red | 5 |
| Apple | red | 25 |
Actual result:
| Name | Colour | no |
|---|---|---|
| Apple | red | 5 |