Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching two list - How can I do it?
Hi
I am looking for assistance ( a formula ?)that would match two records which are almost the same (except for the a few letters. See example below. Vlookup does not help if the two records are not identical. PROJECT HARDWARE & ELECTRICAL DISTRIBUTO PROJECT HARDWARE & ELECTRICAL DISTRIBUTORS Thanks for you help Cheers |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching two list - How can I do it?
Try this€¦
Just add an asterisk symbol as last character in your lookup value. 1st Method Assume that the below value is lying in A1 cell and you want to refer it as a lookup value in your vlookup A1 Cell PROJECT HARDWARE & ELECTRICAL DISTRIBUTO F1 Cell PROJECT HARDWARE & ELECTRICAL DISTRIBUTORS G1 Cell 66 Now I am using this formula by referring a1 cell as lookup value and searching it in F:G columns. =VLOOKUP(A1&"*",F:G,2,FALSE) The above formula will result the value 66. 2nd Method Like the same case you can add the asterisk symbol in the end of the a1 cell value A1 Cell PROJECT HARDWARE & ELECTRICAL DISTRIBUTO* If you are adding the asterisk symbol in the cell value itself then there is no need to use the * in your formula. So make your formula like this =VLOOKUP(A1,F:G,2,FALSE) Note:- Whenever you use the asterisk symbol in the formula, the formula will match only the characters which is lying before the asterisk symbol. A1 Cell PROJECT HARDWARE* F1 Cell PROJECT HARDWARE & ELECTRICAL DISTRIBUTORS G1 Cell 66 =VLOOKUP(A1,F:G,2,FALSE) OR =VLOOKUP(A1&"*",F:G,2,FALSE) The above formula will result the value 66. Both the formulas will match only the word €ťPROJECT HARDWARE€ť and It will not consider the remaining letters which are all followed after €śPROJECT HARDWARE€ś (i.e.) & ELECTRICAL DISTRIBUTORS will be ignored. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "TC" wrote: Hi I am looking for assistance ( a formula ?)that would match two records which are almost the same (except for the a few letters. See example below. Vlookup does not help if the two records are not identical. PROJECT HARDWARE & ELECTRICAL DISTRIBUTO PROJECT HARDWARE & ELECTRICAL DISTRIBUTORS Thanks for you help Cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidating a Matching List | Excel Worksheet Functions | |||
Matching items in 2 list to consolidate to one list | Excel Discussion (Misc queries) | |||
Matching a Value with a list | Excel Discussion (Misc queries) | |||
Copy # fm one list to matching id in list 2? | Excel Worksheet Functions | |||
matching value of top 20 in a list! | Excel Worksheet Functions |