Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Table Lookup | Excel Discussion (Misc queries) | |||
Lookup data in a variable table & retrieve data from a pivot table | Excel Worksheet Functions | |||
How do I lookup a corresponding value in another table | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Lookup Table | Excel Worksheet Functions |