how to match between two list using letters
Hi,
I have two sheets as follows: sheet1 A--------------B 34145 D173abcd 333215 TI242EFG 344233 E0124ABCD Sheet2 A-----------B------------C D-173 abcd ? B-241 efgh ? TI-242 XYZ ? E-0124 STQ ? What I want to do is taking the value from Colum A in sheet1 and put it in column C in sheet 2 using Sheet1 Column B as an indicator. Please note that the Column A in sheet2 has the same data of Column B in sheet1 but there are extra letters and characters. |
how to match between two list using letters
In C1 of sheet2 and copy down;
=INDEX(Sheet1!$A$1:$B$10,MATCH(SUBSTITUTE(A1,"-","") & B1,Sheet1!$B$1:$B$10,0),1) -- Regards Dave Hawley www.ozgrid.com "Jon" wrote in message ... Hi, I have two sheets as follows: sheet1 A--------------B 34145 D173abcd 333215 TI242EFG 344233 E0124ABCD Sheet2 A-----------B------------C D-173 abcd ? B-241 efgh ? TI-242 XYZ ? E-0124 STQ ? What I want to do is taking the value from Colum A in sheet1 and put it in column C in sheet 2 using Sheet1 Column B as an indicator. Please note that the Column A in sheet2 has the same data of Column B in sheet1 but there are extra letters and characters. |
how to match between two list using letters
Hi,
I put it in the first cell of sheet2 which is C7 as follow: =INDEX(Sheet1!$A$2:$B$27037,MATCH(SUBSTITUTE(A7,"-","") & C7,Sheet1!$B$2:$B$27037,0),1) Put it returns 0 please advise? "ozgrid.com" wrote: In C1 of sheet2 and copy down; =INDEX(Sheet1!$A$1:$B$10,MATCH(SUBSTITUTE(A1,"-","") & B1,Sheet1!$B$1:$B$10,0),1) -- Regards Dave Hawley www.ozgrid.com "Jon" wrote in message ... Hi, I have two sheets as follows: sheet1 A--------------B 34145 D173abcd 333215 TI242EFG 344233 E0124ABCD Sheet2 A-----------B------------C D-173 abcd ? B-241 efgh ? TI-242 XYZ ? E-0124 STQ ? What I want to do is taking the value from Colum A in sheet1 and put it in column C in sheet 2 using Sheet1 Column B as an indicator. Please note that the Column A in sheet2 has the same data of Column B in sheet1 but there are extra letters and characters. |
how to match between two list using letters
Try this alternative for fuzzy match n return
In Sheet2, Put in C1, press normal ENTER to confirm will do: =INDEX(Sheet1!$A$1:$A$10,MATCH(TRUE,INDEX(ISNUMBER (SEARCH(B1,Sheet1!$B$1:$B$10)),),0)) Copy C1 down. Modify the ranges to suit. If you need it to be a stricter, case sensitive search, replace SEARCH with FIND in the expression. Success? Wave it, hit the YES below -- Max Singapore --- "Jon" wrote: I have two sheets as follows: sheet1 A--------------B 34145 D173abcd 333215 TI242EFG 344233 E0124ABCD Sheet2 A-----------B------------C D-173 abcd ? B-241 efgh ? TI-242 XYZ ? E-0124 STQ ? What I want to do is taking the value from Colum A in sheet1 and put it in column C in sheet 2 using Sheet1 Column B as an indicator. Please note that the Column A in sheet2 has the same data of Column B in sheet1 but there are extra letters and characters. |
All times are GMT +1. The time now is 01:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com