Looking for Formula

Tiffany A.

Good morning,

I am looking for help on a formula to cross reference customer accounts with their chep account numbers. The trouble I am running into is the customer names are entered slightly different between orders. When I try running a traditional false statement vlookup, hardly any of the account numbers pull over. When I try running a true statement vlookup, too many numbers pull over. I'm hoping someone might be able to tell me how to run an approximate lookup that will search the words in the field to match them? Index match maybe? I'm not sure and looking for ideas.

Nik Fuentes

Hi Tiffany,

as I understand it, your issue is ultimately with an inability to use a direct match for your searches. For example you might be searching for "Todd Barry" but on the order log it might be entered "Todd B" or "Barry, Todd" or some such and not come up. Is that a fair assessment?

If so, I would ask if there is some definite element you could be searching for short of the whole name. Maybe just the last name? If such an element does exist, you could use CONTAINS() functions to search for just the part you know you'll find. You could also use an AND() or OR() function to look for different parts of the name in the same evaluation.

I would use a COLLECT() function and use CONTAINS( value, @cell) for the criteria. That COLLECT() would have to be part of a larger operation, but it would give you your list of matching rows.

Also, it's generally good practice to use INDEX(MATCH()) instead of VLOOKUP() wherever possible, especially when making cross-sheet references. VLOOKUP()s are likely to be badly disrupted if anyone moves a column and they also cover huge swathes of the sheet in question with little black triangles, which can be quite disruptive to those using the source sheet.

Let me know if any of this helps. Happy to circle back if you have more details to share.

-Nik F

Tiffany A.

Thank you, Nik. Is there a way to do this across a large number of instances? The contains function was also an idea I had but I believe I would need to do it for every customer which is well over 500 formulas.