How to use Index/Match or Similar Formula When Criteria is Slightly Different

Hello Everyone!

Let me start by saying thank you to anyone who can provide me some help.

I am currently working on a project for an event planning company where I need to assign booth numbers to almost 300 vendors. For this project I have to combine responses from multiple google forms with open text responses and historical sheets that I did not create. This inherently makes the data terrible and there are multiple spellings for a vendor's name and booth number. For example a booth number can be represented as "S1", "S-01", "S-1", etc.

I am trying to build an Index/Match or Vlookup style formula where it can work around the possibility of the spelling between cells and tabs is slightly off like the example above.

I've searched the internet high and low and just can't find a solution. Can anyone help?

Not really sure, but it may be clearer if you supply a sample workbook. Please read the yellow banner at the top of the page for creating a good sample (with mocked up answers). You might need a "conversion table" where it has a booth number and all the possibilities it could be?

Hi Greg,

Thank you for the quick response. I have created a stripped down example to show you.

There are three tabs - Booth Guide, Historical Assignments, Booth Inventory. As you can see the spelling of the vendor's first and last name are not exactly the same between tabs. You can also see how there are multiple variations for the booth numbering system.

My goal is to pull the information from the Historical Assignments tab to populate in column E of the Booth Guide Tab, and have the correlating information from the Booth Inventory tab to populate in columns E, H, J and L of the Booth Guide Tab. In essence I am trying to create one master tab that collects all the information so I can easily assign booth numbers without having to jump between various tabs.

I hope this explanation helps. Happy to answer any questions you may have.Booth Assignment Example.xlsx

In a good sample file, you should have several scenarios that test the logic you're wanting and it should show YOUR EXPECTED RESULTS.

The only thing I see with Stan(ley) is that his Historical Assignment is Q19. How do you get S-44 as his #1 Booth Choice, or S3 as 2nd, or s05 as 3rd. What does the "Booth Inventory" sheet do for me?

When you mention populating columns H, J, and L, did you mean G, I, and K in your sample or H, J and L?
For the name, I can see automatically removing a tic mark (apostrophe) so Oneil would match O'Neil, but the only way Stan matches Stanley is to create a "Conversion table" (or do you have other logic).
There doesn't seem to be enough samples here, with expected results to really know what to do here yet.