importrange if vlaues in list do not match values in first list

0
votes

I have 2 spreadsheets in Google Sheets, Test1 & Test2. Both containing part #'s in column A. What i need, is for column B in Test1 to return any part #s fromt Test2 that are not already in Test1 column A.

I have a =query(importrange function that is working for if/= Test1 A1, but i cannot get it to look at the entire range of Test1 A:A.

My current formula is

=query(importrange(E1,"Sheet1!A2:A"),"select Col1 where Col1 <> '" & A2 & "'")

Where E1 contains the URL link to Test2.

Test1 https://docs.google.com/spreadsheets/d/1gdHoFMM296wTOgtpa_4o298AeZjrKyieQZWd9wyFLcU/edit?usp=sharing

Test2 https://docs.google.com/spreadsheets/d/1HaKGGkVHfr-EilGbEZP6p-hOADfM12nxRj8tcUsw-Tg/edit?usp=sharing

1
google-sheets

1 Answers

0
votes

In B2 try:

=filter(importrange(E1,"Sheet1!A2:A"), isna(match(importrange(E1,"Sheet1!A2:A")+0, A2:A,0)))

and see if that works (also see example in cell C2 in your shared sheet 'Test1').