1

I have a google sheet that users input data on. These sheet has complete town names. Eventually the data they input gets uploaded into a program which has the town names abbreviated. Im looking for a way to automate basically doing find and replace.

Sheet1- Users input

Town Data
Washington Town Center 123345
Washington Town Center 988765
Fairland Heights 12345

Sheet2 - Reference to the correct names for program.

User Program
Washington Town Center Washington Twn Ctr
Fairland Heights Fair Heights

I basically need to replace the Town names in Sheet1 with the names the Program uses and then all data with that row. A new sheet worth of data is sent every week, so Im looking for the most efficient way to change those names.

I have tried using QUERY and FILTER but cannot seem to find the best way to go about this.QUERY would find all the matches and add as multiple rows as the user sheets can have multiple entries for same town

=QUERY('Sheet1'!A:G,"select * where A like '%"&A2&"%'",0)
player0
  • 124,011
  • 12
  • 67
  • 124
Mr. Anderson
  • 55
  • 6

1 Answers1

1

you need VLOOKUP:

={INDEX(IFNA(VLOOKUP(Sheet1!A:A, Sheet2!A:B, 2, ))), Sheet1!B:G}
player0
  • 124,011
  • 12
  • 67
  • 124
  • Hi, what is the difference between the above and using `=ARRAYFORMULA(INDEX` ? I tried that but it would only return one column where your method returns everything – Mr. Anderson Jan 12 '23 at 15:28
  • 1
    @Mr.Anderson function of both AF and INDEX is the same. INDEX is just shorter to type but if you prefer you can use: `={ARRAYFORMULA(IFNA(VLOOKUP(Sheet1!A:A, Sheet2!A:B, 2, ))), Sheet1!B:G}` – player0 Jan 12 '23 at 16:04
  • 1
    Ok thanks, either is fine for me I just like to breakdown each to fully understand why it works instead of copying. One more questions if you dont mind. Is the use of {} the reason it returns the entire array? – Mr. Anderson Jan 12 '23 at 16:14
  • 1
    @Mr.Anderson yes, array brackets {} are there for constructing the array see: https://i.stack.imgur.com/Y4A72.png – player0 Jan 12 '23 at 16:52
  • I understand how VLOOKUP works but what Im still trying to understand is how `Sheet1!B:G}` is getting returned. In our VLOOKUP we are returning column to which is the name we need for program. But I cant seem to understand what give us the last part – Mr. Anderson Jan 12 '23 at 20:21
  • @Mr.Anderson lets say we have a range `A1:C10` which we need to reference. we can do: `=ARRAYFORMULA(A1:C10)` another way would be doing just: `={A1:C10}` and at this point we can separate columns as we want like for example: `={A1:A10, B1:C10}` or `={A1:A10, B1:B10, C1:C10}` or `={A1:B10, C1:C10}` or even `={A1:C5; {A6:B10, C6:C10}}` we can cut it into whatever pieces we want the only rule is that we need to preserve the symetries – player0 Jan 12 '23 at 23:00
  • The array piece is finally making sense.The `IFNA` piece I thought was to handle #N/A errors however if I remove that piece nothing works. I assumed removing that piece would just give me #N/A Error in certain cells – Mr. Anderson Jan 17 '23 at 14:29
  • @Mr.Anderson `IFNA` is for hiding `#N/A` errors. the propper removal of `IFNA` is: `={INDEX(VLOOKUP(Sheet1!A:A, Sheet2!A:B, 2, )), Sheet1!B:G}` – player0 Jan 17 '23 at 17:16
  • When I use without`IFNA`I receive this error? `Error Result was not automatically expanded, please insert more rows (29183).` – Mr. Anderson Jan 19 '23 at 13:24
  • @Mr.Anderson this kind of error is a result of inserting the formula in row 2. if vlookup is Sheet1!A:A then the formula needs to sit in row 1. if you want it in row 2 use Sheet1!A2:A – player0 Jan 19 '23 at 17:50