Google Sheet pulling data from another spreadsheet if match

Question

So I'm stuck with something. I have two spreadsheets, column A on each are similar but not identical, some values are on one spreadsheet but not the other.

Is it possible for me to pull data from spreadsheet 2, based on if column A has a matching value?

So spreadsheet 2 will have something like:

A            B
item1       100
item2       200
item3       300

and spreadsheet 1 will have something like:

A            B
item1       NULL
item2       NULL
item4       NULL

I want to populate the B columns on spreadsheet 1 based on whether they are on spreadsheet 2 (in this instance, it would populate items 1 and 2)

I've tried VLOOKUP and If statements but don't seem to be getting anywhere.

Answer 1

The VLOOKUP function will do this for you, providing that you set the optional is_sorted parameter to FALSE, so that closest matches will not be returned.

Here's an example. First, our source sheet, Sheet1.

On Sheet2, we use VLOOKUP to pull info from Sheet1, using this formula (from B1, in this case):

=vlookup(A1,Sheet1!$A$1:$B,2,false)
         ^  -------------- ^    ^
         |     |           |    +-- is_sorted
         |     |           +------- return value from col 2
         |     +------------------- Range for search
         +------------------------- search_key

Ok, but that #N/A for item4 is not pretty. We can suppress it by wrapping the VLOOKUP in IFERROR. When the optional second argument of IFERROR is omitted, it will return a blank cell if the first argument evaluates to an error:

=IFERROR(vlookup(A1,Sheet1!$A$1:$B,2,false))

In your example, the data is coming from a separate spreadsheet, not just a different sheet within the current spreadsheet. No problem - VLOOKUP can be combined with IMPORTRANGE as its data source.

=IFERROR(vlookup(A1,IMPORTRANGE("<sheet-id>","Sheet1!A1:B"),2,false))