Proper syntax for wildcards in an IF formula

[phxfitness]

Hello all,

Is there a way to use wildcards in an IF formula?

We receive spreadsheets from multiple people and like to consolidate them into one. Column (D) is where the Company name is populated. Many times the employee names trigger what populates in column (D) and in other instances the Company names are spelled a bit different and we need to consolidate these in order to put them in a pivot table as a one line item. I’m using Excel 2010.

Is there a way to include wildcards in the below IF formula for Joe Smith, Jane Doe, Company1, Company2 and Company3, so, I don’t have to keep adding new Company names into this formula as they change?

=IF(A1="Yes","ABC Company",IF(E1="Smith, Joe","MW",IF(E1="Doe, Jane","MW",IF(I1="MW","MW",IF(D1="Company1 LLC DBA","Company1",IF(D1="Company1 USA INC","Company1",IF(D1="Company2 International","Company2 Inc",IF(D1="Company2 Systems Inc","Company2 Inc",IF(D1="Company2 USA Inc","Company2 Inc",IF(D1="Company3 & Affiliates","Company3",IF(D1="Company3 USA Inc","Company3",D1)))))))))))


Thank you

[Pete_UK]

I think you would be better off setting up a two-column table somewhere, let's say in columns AA and AB, with entries like the following:

Company1 LLC DBA ........... Company1
Company1 USA INC ........... Company1
Company2 International ..... Company2 Inc
Company2 Systems Inc ...... Company2 Inc
Company2 USA Inc ............ Company2 Inc
Company3 & Affiliates ........ Company3
Company3 USA Inc ............ Company3

Then your long formula can become:

=IF(A1="Yes","ABC Company",IF(E1="Smith, Joe","MW",IF(E1="Doe, Jane","MW",IF(I1="MW","MW",IFERROR(VLOOKUP(D1,AA:AB,2,0),D1)))))

You can simply add any more conversions to the bottom of this table, as the need arises. If you have a lot of names in column E, then you could take a similar approach to reduce the formula further.

Hope this helps.

Pete

[Tony Valko]

Is there a way to use wildcards in an IF formula?

The IF function doesn't support the use of wildcards.