Lookup table
 

hi,

I am using a lookup table to bring the value from one workshreet to another.
My lookup_value is slightly different than value_table_array. Below ae some
examples.

How can I match value to the table as they are the same customer?

Lookup Value Value_table_array

First American Title Co First American
Raytheon Company. Raytheon Co.
Charles River Lab Charles River
Endologix Endologix Inc
Sanford Health Sanford Health
Schering Schering Corporation.
TBC Parent TBC Parent Holding
Texas Health Resources Texas Health Resources
The Hertz Corp The Hertz


Dinesh

Lookup table
 

You could try something like this:

=VLOOKUP(LEFT(A1,8)&"*",table,column,0)

because VLOOKUP supports wildcards. This is based on your examples,
but you might need to tweak the 8 to cater for your real data - this
was for the shortest string (Schering).

Hope this helps.

Pete

On Mar 1, 7:43*pm, Dinesh wrote:
hi,

I am using a lookup table to bring the value from one workshreet to another.
My lookup_value is slightly different than value_table_array. Below ae some
examples.

How can I match value to the table as they are the same customer?

Lookup Value * * * * * * * * * *Value_table_array

First American Title Co * * * * First American
Raytheon Company. * * * * * * * * * * * Raytheon Co..
Charles River Lab * * * * * * * * * * * * * * *Charles River
Endologix * * * * * * * * * * * * * * * * * * *Endologix Inc
Sanford Health * * * * * * * * * * * *Sanford Health
Schering * * * * * * * * * * * * * * * * * * *Schering Corporation.
TBC Parent * * * * * * * * * * * * * *TBC Parent Holding
Texas Health Resources * * * * * * * *Texas Health Resources
The Hertz Corp * * * * * * * * * * * *The Hertz

Dinesh

Lookup table
 

You could try combining a countif with your lookup
add a column to bring back the closest name if an exact match is not found

if your countif = 0 then use a vlookup with a true and have it display the
closest match so that you can decide if you want to use it or not

=IF(COUNTIF(C293:C296,B293)=0,VLOOKUP(B293,C293:C2 96,1,TRUE),"")

Hope this gets you started on a solution

"Pete_UK" wrote:

You could try something like this:

=VLOOKUP(LEFT(A1,8)&"*",table,column,0)

because VLOOKUP supports wildcards. This is based on your examples,
but you might need to tweak the 8 to cater for your real data - this
was for the shortest string (Schering).

Hope this helps.

Pete

On Mar 1, 7:43 pm, Dinesh wrote:
hi,

I am using a lookup table to bring the value from one workshreet to another.
My lookup_value is slightly different than value_table_array. Below ae some
examples.

How can I match value to the table as they are the same customer?

Lookup Value Value_table_array

First American Title Co First American
Raytheon Company. Raytheon Co..
Charles River Lab Charles River
Endologix Endologix Inc
Sanford Health Sanford Health
Schering Schering Corporation.
TBC Parent TBC Parent Holding
Texas Health Resources Texas Health Resources
The Hertz Corp The Hertz

Dinesh

.

Lookup table
 

I use following formula:
=IF(COUNTIF(Sheet2!$I$291:$I$536,Rollforward!A5)=0 ,VLOOKUP(Rollforward!A5,Sheet2!$I$291:$J$536,2,0), "")


The result is either "blank" cell or "#N/A"

I pull the customer name from two different sorces. so sometime customer
name is abbreviated or if it is a corporation vs Corp or Corp. (with
period/without period) etc.

Dinesh



"pmartglass" wrote:

You could try combining a countif with your lookup
add a column to bring back the closest name if an exact match is not found

if your countif = 0 then use a vlookup with a true and have it display the
closest match so that you can decide if you want to use it or not

=IF(COUNTIF(C293:C296,B293)=0,VLOOKUP(B293,C293:C2 96,1,TRUE),"")

Hope this gets you started on a solution

"Pete_UK" wrote:

You could try something like this:

=VLOOKUP(LEFT(A1,8)&"*",table,column,0)

because VLOOKUP supports wildcards. This is based on your examples,
but you might need to tweak the 8 to cater for your real data - this
was for the shortest string (Schering).

Hope this helps.

Pete

On Mar 1, 7:43 pm, Dinesh wrote:
hi,

I am using a lookup table to bring the value from one workshreet to another.
My lookup_value is slightly different than value_table_array. Below ae some
examples.

How can I match value to the table as they are the same customer?

Lookup Value Value_table_array

First American Title Co First American
Raytheon Company. Raytheon Co..
Charles River Lab Charles River
Endologix Endologix Inc
Sanford Health Sanford Health
Schering Schering Corporation.
TBC Parent TBC Parent Holding
Texas Health Resources Texas Health Resources
The Hertz Corp The Hertz

Dinesh

.