Lookup table

D

Dinesh

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
 
P

Pete_UK

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
 
P

pmartglass

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:C296,1,TRUE),"")

Hope this gets you started on a solution
 
D

Dinesh

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top