GOOGLE SPREAD SHEET 2 ( TABLE )

S.NO....NAME.............ID

1.     **(emptycell)**  (emptycell)     
2.      ELANGO N         1001      
3.      RAJA E           1002      
4.      RAJINI S         1003        

My formula is ( using CellA6 as search box in googlespread sheet 1)

=IFERROR(VLOOKUP(A6,IMPORTRANGE("https://docs.google.com/spreadsheets/d/13xiSA0hIJx2inhykUSRIi1pqfKJakMTu42Atj9Q-NCM/edit#gid=0","Sheet1!$A$2:$B$5"),3,0), "NO SUCH NAME")

CELL A6 IS THE SEARCH CELL IN SHEET 1

IN GOOGLE SPREAD SHEET 1 IN A6 CELL,

WHEN I TYPE NAME PRESENT IN THE TABLE I CAN GET ID AS A RESULT.

WHEN I TYPE THE NAME WHICH ARE NOT PRESENT IN THE TABLE I CAN GET "NO SUCH NAME" AS A RESULT.

IF I HAVE N'T TYPE ANY IN NAME IN CELL A6( SEARCH CELL ), I CAN GET THE RESULT "NO SUCH NAME"

Can I GET IF CELL A6 IS EMPTY THE RESULT ALSO WILL BE EMPTY CELL ? ( I have started to learn this kind of stuff from this month .This forum may belongs to genius. And also I am new to this website. If I make any mistakes pls forgive me )

CodePudding user response：

use:

=IF(A6="",,IFERROR(VLOOKUP(A6, 
 IMPORTRANGE("13xiSA0hIJx2inhykUSRIi1pqfKJakMTu42Atj9Q-NCM", 
 "Sheet1!$A$2:$B$5"),3,0), "NO SUCH NAME"))

CodePudding user response：

Here's a solution using a QUERY formula. There are 2 advantages for using QUERY:

1. If you dont remember the full name you can type in Sheet1!A6 only part of names
2. the QUERY can be case insensitive so you can type in upper or lower case in Sheet1!A6

Copy and paste the following formula to the cell in Sheet 1 where you want your search result answer. Ideally in B6 (can be anywhere BUT A6 where you have the search query cell):

=IFNA(
    IF($A$6="","",
        IFS(
            COUNTA(QUERY(Sheet2!A2:C,"select C where lower(B) contains lower('"&$A$6&"')",0))=1,QUERY(Sheet2!A2:C,"select C where lower(B) contains lower('"&$A$6&"')",0),
            COUNTA(QUERY(Sheet2!A2:C,"select C where lower(B) contains lower('"&$A$6&"')",0))>1,COUNTA(QUERY(Sheet2!A2:C,"select C where lower(B) contains lower('"&$A$6&"')",0))&" MATCHES"
        )
    ),
"NO SUCH NAME")

Whet this formula will do is take any letter combination typed in the cell Sheet1!A6, then perform a search in the table in Sheet2! (not the first row).

If Sheet1!A6 is empty, the formula will return empty.

If Sheet1!A6 contains a single match, it will return the ID

If there is no match with Sheet1!A6, it will return NO SUCH NAME

Now here I added one more case...

If Sheet1!A6 contains a combination of characters which match with 2 or more names, the formula will count how many names match and will return X MATCHES