Excel 2007: VLOOKUP Based on a Value Appearing Within A Cell 3

[greengo204]

Hi,

I have 2 tables both with a field 'COMPANY_NAME/CUSTOMER_NAME' but they appear in a different format on each table e.g.

tableA
             
COMPANY_NAME/CUSTOMER_NAME     AMOUNT
Mr John Smith                     100 
Doctor Andrew Brown               500
Microsoft                        1000

tableB

COMPANY_NAME/CUSTOMER_NAME     AMOUNT
John (Ben)Smith                   100 
Andrew (Albert) Brown             500
Microsoft                        1000

I am trying to create a VLOOKUP that can compare the list and return the results from Table A e.g.

tableB
             
COMPANY_NAME/CUSTOMER_NAME     AMOUNT  VLOOKUP_RESULT
John (Ben)Smith                   100            100
Andrew (Albert) Brown             500            500
Microsoft                        1000           1000

Note 1: 'COMPANY_NAME' is consistant in both tables therefore a straight VLOOKUP will work.
Note 2: 'CUSTOMER_NAME' in TableA includes titles (e.g. Mr, Mrs) with no middle names.
Note 3: 'CUSTOMER_NAME' in TableB includes middle names but no titles.


This is what I have so far:

=if(isnumber(search("(",A2),VLOOKUP(LEFT(A2,SEARCH("(",A2)-2)&MID(A2,SEARCH(")",A2)+1,LEN(A2)-(SEARCH(")",A2)+1)), 2 ),tableA,2,0),VLOOKUP(A2,tableA,2,0)

My problem is that tableA is in a diferent format to what I am querying, Is it possible to use VLOOKUP to search within a cell and return a result e.g. does John Smith exist in any cell within column A in tableA if so perform a vlookup on this argument.

Please could some assist me with my formula or suggest an alternative method.


Thanks

Kyle

 

[SkipVought]

Hi,

Can you with absolute certainty predice that EVERY SINGLE customer in your two tables adhere to these rules?

1. the second and third WORDS in table A, correspond the the first and last words in table B IF the word count > 1

2. If the word count = 1 then expect a direct correlation.

If that's the case, then use DATA > Text to columns to PARSE the COMPANY_NAME/CUSTOMER_NAME column, using [SPACE] and [RIGHT PARENTHESIS] as delimiters. The result should look like...
[tt]
tableA

COMPANY_NAME/CUSTOMER_NAME AMOUNT
Mr John Smith 100 Mr John Smith
Doctor Andrew Brown 500 Doctor Andrew Brown
Microsoft 1000 Microsoft

tableB

COMPANY_NAME/CUSTOMER_NAME AMOUNT
John (Ben)Smith 100 John (Ben Smith
Andrew (Albert) Brown 500 Andrew (Albert Brown
Microsoft 1000 Microsoft
[/tt]
From this point you can easily concatenate the names for lookup purposes and perform you lookup. I'd use INDEX & MATCH rather than VLOOKUP, however.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[greengo204]

SkipVought

Thanks for your advice on this. The text to column method does work but I was hoping to find a formula based method that would work.

Suppose my question is, is it possible to complete a vlookup (or alternative) based on a string appearing in the target range?:

e.g.

tableA:
NAME AMOUNT
mrs Joan Smith 100


tableB:
NAME AMOUNT FORMULA_RESULT
Joan Smith 500 100

Kind Regards,

Kyle

 

[SkipVought]

That is a FAR cry from your original requirememt!

Check out the RIGHT(), FIND, LEN() functions, as long as the format in table A is ALWAYS
[tt]
[Some Word] [SPACE] [The lookup value]
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

If you want to do this with formulae, I don't see any way to avoid putting a helper column in one of the tables, containing a trimmed version of the data.

The trouble is that vlookup must search for a match on the entire string. You can trim the search text to remove things specific to table B, but you can't then use this text to search against table A, because table A also contains extra data not available to table B. You don't have the extra bits (titles) with which to expand your search term.

If you can add a helper column, you can use string functions as Skip suggested. I have to admit, though, I don't feel comfortable with this two-table system with two different formats of combined bits of names. Everyone knows that names can be ambiguous (you can have two Doctor Andrew Browns), but it's doubly messy when you can have a name that is ambiguous in one table, but not in the other (Andrew (James) Brown and Andrew (John) Brown).

(A better approach might be to learn from the database guys, and give everyone a unique key ID.)

 

[Gavona]

Suppose my question is, is it possible to complete a vlookup (or alternative) based on a string appearing in the target range?:

I haven't read this thread properly but wonder if wildcards within the vlookup might help:
thread68-1253790



Gavin

 

[greengo204]

Skipvought:

Yes you right, I think I need to improve expressing my requirements


Lionelhill:

99.9% of the time I would use a unique key or request the DBAs to provide me with something unique. In this situation I have no option for this due to the nature of the source of the data (bank accounts and contract names - bank details do not have to be the same as the contract)

Gavona:

Thanks for the post I never realised you could use wildcards in VLOOKUPS!!

Results:

I managed to create a lookup that works for what I origannaly intended but never considered Joint bank accounts so the formula falls over in these instances. I can't see a way around this as the Joint name does not exist on the other table.

formula:

TableA & TableB are in the same worksheet.

=IF(ISNUMBER(SEARCH("(",F4)),VLOOKUP("*"&(LEFT(F4,SEARCH("(",F4)-2)&MID(F4,SEARCH(")",F4)+1,LEN(F4)-(SEARCH(")",F4)+1)))&"*",A:B,2,0),VLOOKUP("*"&F4&"*",A:B,2,0))

The above works for all cases except joint accounts and instances where the bank account is entirely different to the contract name.

 

[Gavona]

Glad to have been able to pass on that good advice about wildcards from Zack and Ken.

Gavin