Cross-Referencng numbers between worksheets

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

Colin Hayes

HI All

I need a little help referencing some numbers between Sheet1 and Sheet2.

On sheet 1 in column A I have a list of numbers. Column B is blank

On sheet 2 in column A I also have a list of numbers , some matching
those on Sheet1. Column B has a list of alphanumeric codes each next to
each number.

What I need to do is match the numbers in column A on sheet 1 against
the numbers in column A in sheet 2.

Where there is a match , the equivalent alphanumeric code is placed
against the matching number in column B of sheet A. Where is no match ,
the relevant cell on sheet A is left blank.

So , for example

*Before : *

Sheet1

A B
1
2
3
4
5
6
7
8
9
10

Sheet2

A B
1 S1245C
2 S1246C
5 S1247C
7 S1248C
8 S1249C
9 S1250C
10 S1251C


*After : *

Sheet 1

A B
1 S1245C
2 S1246C
3
4
5 S1247C
6
7 S1248C
8 S1249C
9 S1250C
10 S1251C


Where the numbers in columns A on both sheets match , the code in B of
sheet2 is brought across to sheet1. Where no match is found , the cell
in Sheet1 is left blank.


Can someone assist with this?

Grateful for any assistance .
 
One good way is via index/match

In Sheet1,
In B1:
=IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"",INDEX(Sheet2!B:B,MATCH(A1,Sheet2!
A:A,0)))
Copy down

Adapt the above easily to suit everywhere else,
you can match on any col, return any other col to the left or right of
the match
MATCH(A1,Sheet2!A:A,0) ---> this is the match, match on col A in
Sheet2
INDEX(Sheet2!B:B ---> this is the return col
IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"", ... --> this is the error trap to
return blanks: "" where there's no match found
 
Max said:
One good way is via index/match

In Sheet1,
In B1:
=IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"",INDEX(Sheet2!B:B,MATCH(A1,Sheet2!
A:A,0)))
Copy down

Adapt the above easily to suit everywhere else,
you can match on any col, return any other col to the left or right of
the match
MATCH(A1,Sheet2!A:A,0) ---> this is the match, match on col A in
Sheet2
INDEX(Sheet2!B:B ---> this is the return col
IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"", ... --> this is the error trap to
return blanks: "" where there's no match found
Click to expand...


Hi Max

OK Thanks for getting back with that.

Perfect solution - just what I needed



Best Wishes
 
Back
Top