Search a value from another tab/sheet in google sheets based on cell reference

Question

I'm trying to create a search page in google sheets where the user enters a value in one cell(s) and then other cells auto-populate with values from another tab/sheet based on the value entered in the search cell(s).

I'm trying to use VLOOKUP up in an array but it gives either error or N/A.

I have 'sheet1' with data from a google forms (columns from A to BN) and I'm trying to make a search page in 'Sheet2' where on top the user can write for example the ID-number, and I want to grab the value of a column from 'sheet1' that matches the ID-number written on the same row for 'sheet1'.

I've already checked:

There are others but I'm not looking for any scripts.

In simple form the formula would be:

=VLOOKUP(B3,'sheet1'!A:BN,40,False)

But because I have too many columns and I'll be needing to grab other values too I'm using:

=ArrayFormula(IF(LEN(B3),VLOOKUP(B3,{'sheet1'!A:BN},{iferror(match("ID-number",'sheet1'!A1:BN1,0),"No match")},FALSE),""))

Explanation:

B3 is the cell where the user inputs the id number value

Then ...:

{'sheet1'!A:BN},{iferror(match("ID-number",'sheet1'!A1:BN1,0),"No match")}

is where I'm looking for the number of the column of the title of value I'm looking for, in this case, it is AN, which would be the column 40 but it could be any other.

And then the ArrayFormula is because sheet1 is data from a Google form that is on use and increasing rows constantly.

It gives me an error of #N/A because it doesn't find the value (input in B3 in sheet 2) in the range (tab sheet1 in all the columns from A to BN that have values)

Example sheet with working answer is [here]: (https://docs.google.com/spreadsheets/d/1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8/edit?usp=sharing)

NOTE: The values to search/return are on the same row in sheet 1 and when a new search is entered then only the row changes.

I made an example sheet [here] (docs.google.com/spreadsheets/d/…) It has 2 tabs with the example code being in 'searcher' tab with various codes I tried (including the one with the answer from Goran below- — deags
I see you have there 3 rows under heading which may or may not be the same in a value. in such case, you want to return which value? all unique, all of them? first?, second?, third?, last?, comma separated?? i.stack.imgur.com/84tQp.png — player0
Im looking for just 1 value in each cell of the search results corresponding to the row that has the value entered in the search cells above. In excel the formula would be : =BDEXTRAER(Tabla_name[#Todo],Tabla_name[[#Encabezados],[RPE]],B3:$E$3) — deags

player0 player0 · Accepted Answer · 2019-05-28T17:20:34

=ARRAYFORMULA(
 IF(B3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(B3,            data!A:E,  {1,2,3,5}, 0)), CHAR(10))), "♦", ),
 IF(C3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(C3, {data!C:C, data!A:E}, {2,3,4,6}, 0)), CHAR(10))), "♦", ), 
 IF(D3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(D3, {data!E:E, data!A:E}, {2,3,4,6}, 0)), CHAR(10))), "♦", ), ))))

UPDATE:

=IFERROR(ARRAYFORMULA(
 IF(B3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(B3, {data!AN:AN, data!A:BN},  {41,38,19,11,55}, 0)), CHAR(10))), "♦", ),
 IF(C3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(C3, {data!AK:AK, data!A:BN}, {41,38,19,11,55}, 0)), CHAR(10))), "♦", ), 
 IF(E3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(E3, {data!BJ:BJ, data!A:BN}, {41,38,19,11,55}, 0)), CHAR(10))), "♦", ), 
 IF(D3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(D3, {data!R:R, data!A:BN}, {41,38,19,11,55}, 0)), CHAR(10))), "♦", ), ))))), "no match found")

Search a value from another tab/sheet in google sheets based on cell reference

2 Answers

UPDATE: