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

On Jun 9, 8:31*am, Colin Hayes <Co...@chayes.demon.co.uk> wrote:
> 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 .

In article
<ad159b58-a32c-4092-8944-(E-Mail Removed)>, Max
<(E-Mail Removed)> writes
>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


Hi Max

OK Thanks for getting back with that.

Perfect solution - just what I needed



Best Wishes

Pleasure`, do give the stars a good happy ring

On Jun 9, 11:51*am, Colin Hayes <Co...@chayes.demon.co.uk> wrote:
> Hi Max
>
> OK Thanks for getting back with that.
>
> Perfect solution - just what I needed
>
> Best Wishes- Hide quoted text -