match last 4 digits in seperate sheets and return from column j?

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

Hi, I am trying to search a two columns in two worksheets
and if I get a match have a return from a corresponding
cell in the same row.

Of a 8-14 digit number only the last four will match. So,
how do I search two columns in seperate workbooks and
return a value in the same row as one of the matches
(sheet 1) on the other sheet (sheet 2)?

What I have I can't get to work, Can someone help? Am I
even on the right track?

Todd


=IF(MATCH(RIGHT(F5:F50,2),RIGHT(sheet2!G5:G50,2),1),Sheet2!
J5:50,"No Match") ????

Sheet 1 sheet 1 Sheet 2 Sheet 2
22222 (if a match =15) 22222 15
4444 (if a match =14) 4444 14
5555 (if a match =13) 5555 13
6666 (if a match =12) 6666 12
 
Hi Todd!

Here's one way:

Assume this string is in cell A5 of sheet1,
10202222 and you want to search sheet2 col F1:F100 for a
match of the last 4 chars and if found return the
corresponding value in sheet2 col G:

=IF(ISNA(INDEX(sheet2!G1:G100,MATCH(RIGHT(A5,4),RIGHT
(sheet2!F1:F100,4),0))),"no match",INDEX(sheet2!
G1:G100,MATCH(RIGHT(A5,4),RIGHT(sheet2!F1:F100,4),0)))

Entered as an array - CTRL,SHIFT,ENTER

Biff
 
Thanks Biff, Its working great! What if I have more than
one occurance of a number? Can sumproduct be used with
this?


=IF(ISNA(INDEX(G2:G101,MATCH(RIGHT(F2,2),RIGHT
(F2:F101,2),0))),"no match",INDEX(J2:J101,MATCH(RIGHT
(F2,2),RIGHT(F2:F101,2),0)))

(I took out the sheet names to make it simpler to play
with).
 
What if I have more than one occurance of a number?

That's a good question! It'll take someone a lot smarter
than me to figure out that one!

The MATCH will always find the first occurance.

I don't know what you mean by: "Can sumproduct be used
with this?"

Biff
 
Back
Top