how to match between two list using letters

J

Jon

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.
 
O

ozgrid.com

In C1 of sheet2 and copy down;

=INDEX(Sheet1!$A$1:$B$10,MATCH(SUBSTITUTE(A1,"-","") &
B1,Sheet1!$B$1:$B$10,0),1)
 
J

Jon

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?
 
M

Max

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[/QUOTE]
 

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