Google-sheets – How to import values from the 2nd sheet based on the colum of the 1st sheet

formulasgoogle sheetsgoogle-sheets-arrayformulavlookup

I have two sheets –

Sheet1 includes:

col 1  | col 2 
[Name] | [phones ]

Sheet2 includes:

col 1  | col 2
[Name] | [e-mail]

I want to importrange if the name on Sheet1 is equal to name on Sheet2.

use VLOOKUP formula. paste this formula in cell C1 on Sheet2:

=ARRAYFORMULA(IFERROR(VLOOKUP(A:A, Sheet1!A:B, 2, 0), ))

=ARRAYFORMULA(IFERROR(VLOOKUP(FILTER(Sheet1!A1:A,
 ISNUMBER(SEARCH("*"&A1:A&"*",A1:A))), Sheet1!A1:B, 2, 0), ))

In SHEET2 in cell A1, add the following formula:

=IF(AND(Sheet1!A1="s";Sheet1!B1="s";Sheet1!D1="s";Sheet1!F1="s");"s";"invalid")

Is says:

If A1, B1, D1 and F1 in sheet 1 all contain an s then write s else write invalid

Try:

=COUNTIFS('Form Responses 1'!$B:$B,Sheet2!$A2,'Form Responses 1'!$C:$C,"*"&Sheet2!B$1&"*")

(One fewer $.)

The Sheet2! are not required.