How can I dynamically import value from the same workbook using a formula

2
votes

I'm trying to employ ArrayFormula to import value from another sheet inside the same workbook, but I can't find anything that works except ImportRange, and that requires the current workbook's URL which is tedious.

I have two sheets set up: "Sheet Name1" and "Sheet Name2" (notice the spaces) inside the same workbook. Inside "Sheet Name1" I have a list of people:

   A      B        C
1 Bob   Smith      http://bobsmith.com
2 Sally Buble      http://sallysmelly.com
3 Tom   Shlonsky   http://tomswebsite.com
4

There is their first name, last name, and a URL they own

in "Sheet Name2" I have the following:

   A      B        C                      D                          E
1 Bob   Smith             =MATCH(B1, 'Sheet Name1'!B:B, 0) =CONCAT("'Sheet Name1'!C", D1)
2 Sally Buble      
3 Tom   Shlonsky   
4

I'd like cell C in "Sheet Name2" to also display those websites. HOWEVER, I can't just make a reference like ='Sheet Name1'!C1 because if Sally gets deleted in "Sheet Name1" (i.e. her row is deleted, not cleared but DELETED), then Sally in "Sheet Name2" will display Tom's website.

I tried the following formula set:

  1. In cell D1 of "Sheet Name2" I go =MATCH(B1, 'Sheet Name1'!B:B, 0) which gives me 1
  2. In cell E1 of "Sheet Name2" I go =CONCAT("'Sheet Name1'!C", D1) which gives me 'Sheet Name1'!C1
  3. But now when I go =ArrayFormula(E1) I get 'Sheet Name1'!C1 again. While if I go =ArrayFormula('Sheet Name1'!C1) I get the URL I want, http://bobsmith.com.

I know I can use ImportRange but that requires the URL of the current workbook and this workbook will be duplicated many times with different people working on it, so I'd like to remove that extra step.

1
google-sheetsmatchgoogle-sheets-formulaarray-formulasgs-vlookup

1 Answers

0
votes

try VLOOKUP like:

=ARRAYFORMULA(IFERROR(VLOOKUP(A1:A&B1:B, 
 {'Sheet Name1'!A1:A&'Sheet Name1'!B1:B, 'Sheet Name1'!C1:C}, 2, 0)))

0