[Solved] Multiple Find and Replace Based On List of Values

Discuss the spreadsheet application
Post Reply
kevin_itgz
Posts: 2
Joined: Thu Sep 08, 2022 10:20 pm

[Solved] Multiple Find and Replace Based On List of Values

Post by kevin_itgz »

Hello,

I need a little help constructing the right function without it getting unwieldy. I have a high school football schedule I've downloaded that has around 4000 games for the entire season for about 420 teams. The problem is, it only gives me the school names on the schedule and I'm trying to add the mascot to each school name. I have a list of the unique school names and their mascots in two columns on the sheet, but I'm trying to find a way possibly with a formula to find the school name throughout the 4000 games and replace it with the concatenated school name with mascot. I can get an IF formula to work for the first schools games but it looks like I need an IF formula for each school, and 420 of those would get crazy. Here's a sample table.

School          Home                         Visitor             School Name     Mascot
ACE Charter     ACE Charter                  Crawford County     ACE Charter     Gryphon
ACE Charter     Georgia Military College     ACE Charter         Adairsville     Tigers
ACE Charter     ACE Charter                  Pataula Charter     Alcovy          Tigers
Adairsville     Adairsville                  Cherokee Bluff      Alexander       Cougars
Adairsville     Rabun County                 Adairsville         Allattoona      Buccaneers
Adairsville     Adairsville                  Cass                Alpharetta      Raiders

My apologies in advance for the data not being in a table. Since its my first time posting in this forum I wasn't sure how to build out the table appropriately.

Thanks!
 Edit: Added formatting for table -- MrProgrammer, forum moderator  
Last edited by MrProgrammer on Thu Sep 15, 2022 6:45 pm, edited 2 times in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
Open Office 4.1.10 / Windows 11
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Multiple Find and Replace Based On List of Values

Post by FJCC »

Please upload a Calc file as an example. A file with just a few rows of data is sufficient. To upload the file, click Post Reply and look for the Attachments tab just below the box where you type a response. Please show the data you have and an example of the result you want.
I think the VLOOKUP function will do what you want, but it will be easier to tell once a file is available.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
kevin_itgz
Posts: 2
Joined: Thu Sep 08, 2022 10:20 pm

Re: Multiple Find and Replace Based On List of Values

Post by kevin_itgz »

Thank you in advance for your assistance. Attached is the file. On the first sheet of the file is a few example rows and the master list of what I have now. On the second sheet in the file is the desired output/end goal I'm looking for.
Attachments
ExampleFileForDiscussionPost.ods
(17.73 KiB) Downloaded 62 times
Open Office 4.1.10 / Windows 11
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Multiple Find and Replace Based On List of Values

Post by FJCC »

I put the desired result in columns G, H and I of the first sheet. Take a look at the Help section on the VLOOKUP function and see if my formula makes sense.
Attachments
ExampleFileForDiscussionPost_fjcc.ods
(17.36 KiB) Downloaded 63 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Post Reply