Cross referencing two sheets and pulling in values

Question

I have a Google sheet with multiple sheets in a single document. A generalized version of what I have is something like this:

 Sheet 1: 

    A    B    C   
 | AA |    |    |  
 | BB |    |    |  
 | GG |    |    |  
 | KK |    |    |  
 | RR |    |    |  
 | CC |    |    |  
 | GG |    |    |  
 | XX |    |    |  

 Sheet 2: 

    A    B    C
 | AA |    | 01 |  
 | BB |    | 02 |  
 | CC |    | 03 |  
 | DD |    | 04 |  
 | EE |    | 05 |  
 | FF |    | 06 |  
 | GG |    | 07 |  
 | HH |    | 08 |  
 | II |    | 09 |  
 | JJ |    | 10 |  
 | KK |    | 11 |  
 | LL |    | 12 |  
 | MM |    | 13 |  
 | NN |    | 14 |  
 | OO |    | 15 |  
 | PP |    | 16 |  
 | QQ |    | 17 |  
 | RR |    | 18 |  
 | SS |    | 19 |  
 | TT |    | 20 |  
 | UU |    | 21 |  
 | VV |    | 22 |  
 | WW |    | 23 |  
 | XX |    | 24 |  
 | YY |    | 25 |  
 | ZZ |    | 26 |

What I want to do is write a formula that looks at the items in Sheet1!A;A, finds the corresponding entries in Sheet2!A:A and matches up the values from Sheet2!C:C with their corresponding entries in Sheet1, placing them in Sheet1!C:C.

Ideally, it would output this:

 Sheet 1: 

    A    B    C   
 | AA |    | 01 |  
 | BB |    | 02 |  
 | GG |    | 07 |  
 | KK |    | 11 |  
 | RR |    | 18 |  
 | CC |    | 03 |  
 | GG |    | 07 |  
 | XX |    | 24 |  

Instead, all I seem to be able to yield (if anything) is this:

 Sheet 1: 

    A    B    C   
 | AA |    | 01 |  
 | BB |    | 02 |  
 | GG |    | 03 |  
 | KK |    | 04 |  
 | RR |    | 05 |  
 | CC |    | 06 |  
 | GG |    | 07 |  
 | XX |    | 08 |  

So far, I have tried the following to no avail:

 =FILTER(Sheet2!C:C, MATCH(Sheet1!A:A, Sheet2!A:A, 0))

 =QUERY({Sheet1!A:A, Sheet2!A:A, Sheet2!C:C}, "SELECT Col3 WHERE Col2 = Col1 AND Col1 != '' ",0)

 =QUERY({Sheet1!A:A, Sheet2!A:A, Sheet2!C:C},"SELECT Col3 WHERE Col1 = Col2",1)

(Along with many other variations.)

The data in Sheet1 will be added to regularly, with no definitive end point, so I want the formula to extend infinitely down the sheet (thus my operations on Sheet1!A:A).

I would also like to be able to QUERY or FILTER it (or some other function) rather than using a custom script.

Answer 1

This is what the command VLOOKUP is for.

Put in C1 =VLOOKUP(A1, Sheet2!A:C, 3, FALSE) and drag down the row. Here,

• A1 is the entry to search for
• Sheet2!A:C is the range in which the first column (A) is to be searched
• 3 is the column number from which to take the result (that is, C)
• FALSE requires exact match, and does not assume the keys are sorted in the second sheet. If they are, consider omitting this (after reading the docs), but then if there is no match, you'll get a neighboring value instead of #N/A error.

You can also make the formula expand automatically as new data are added, with the help of ARRAYFORMULA:

=ARRAYFORMULA(IF(LEN(A1:A), VLOOKUP(A1:A, Sheet2!A:C, 3, FALSE), ""))

The logic is same as above, except there's a condition saying that empty cells in A column shouldn't be passed to VLOOKUP.