Fetching data from other Spreadsheet by comparing some values

Question

I have a sample Spreadsheet such as

And I want to import data from this spreadsheet to another spreadsheet, but after comparing some data, such as

My noob formula for the selected cell name in the second image:

=Query(ImportRange("URL of first spreadsheet","sheet!B:D"),"Select Col2 where Col2=A.....(what to add here? this column 'A' is not working for comparing the names)

My Approach:

I will compare the values from the import range with the existing column i.e. previousSpreadsheet.name.A=thisSpreadsheet.this_name.A and compare every name and SELECT values accordingly for the next three columns (in yellow). I hope I'm clear.

What should I add here? Is my approach right? If not, What are the alternatives to achieve this? Thanks.

Answer 1

EDIT

(following OP's request)

Also, can I compare two cols with one? such as where LOWER(Col2)='"&LOWER(A2)&"' or '"&F2&"' or Col2=A2 || F2 something like this? for comparing two cols with one?

If you start using more than one names as variables, you may want to consider using other alternatives for the where clause, like matches

=Query(ImportRange("URL of first spreadsheet","sheet!B:D"),
"Select Col2 where Lower(Col2) matches'"&LOWER({A2&"|"&F2})&"'")

---

Original answer

Please use

=Query(ImportRange("URL of first spreadsheet","sheet!B:D"),"Select Col2 where Col2='"&A2&"'")

(where A2 is the name you need)

Pay notice to the syntax referring the cell: single quotes ' double quotes" ampersand & cell A2 and again &"'.
No spaces in between