Google Sheets Query using important range and a dynamic cell reference

Question

I have a roster of students (Sheet 1) and I need a formula to search another sheet (Sheet 2) to see if each student on Sheet 1 is also on Sheet 2 and if they return the word "yes"/ if they are not I would like the cell to remain blank.

This is the formula I have: =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1395p5J5oN4vYLPeyAdUQDZluwxWU53NYJPiqeHdXSEw/", "Quarter 3!A3:H"), "Select 'yes' where Col1="&$A7&"")

The formula is not working properly.

For students that are on Sheet 2 the formula is returning two rows of data (two rows of "yes) and the first instance of "yes" include characters that I did not include in the query:

For students not on Sheet 2, the formula is returning a yes with the extra characters "yes"().

I feel like I should be using an IF function, but I could not get it to work with IMPORTRANGE

player0 player0 · Accepted Answer · 2021-01-21T23:18:07

use:

=QUERY(IMPORTRANGE(
 "1395p5J5oN4vYLPeyAdUQDZluwxWU53NYJPiqeHdXSEw", "Quarter 3!A3:H"), 
 "select 'yes' where Col1="&$A7&"
  label 'yes''')

Google Sheets Query using important range and a dynamic cell reference

1 Answers