compare cell with a range

[bagus]

I have two worksheets :

Sheet 1:

A1 Country
B1 Product Code
C1 Product (empty cell - need to insert result of the formula in it)

Sheet 2:

A1-A1000 Product Code
B1-B1000 Product Name

I need to compare B1 (Sheet 1) with the range in A1-A1000 (Sheet 2) an
if they match than to retrieve the value from B cell (Sheet 2) an
insert it into C cell (sheet 1).

Can anyone help me. I know that it is trivial, but i have not made i
work till now.

/bagu

 

[Don Guillett]

Have a look in vba help index for FINDNEXT. There is an example.

 

[Dave Peterson]

=if(iserror(vlookup(b1,sheet2!a:b,2,false)),"",vlookup(b1,sheet2!a:b,2,false))

Is one way.

 

[bagus]

HI Dave,

thanks for the quick reply. I have tried to match your instructions an
have managed only to get #NAME? as a result.

Anything that I am doing wrong?

Product codes contain both letters and numbers, and in addition symbol
(rare). Do I have to format the column to a specific format?

regards,

/bagus

 

[Dave Peterson]

Post the formula you tried.

Copy it from the formula bar (from excel) and paste it into your response.

 

[bagus]

Here is the formula I used:

=IF(ISERROR((VLOOKUP(Sheet1!C1;Sheet2!A:E;2;FALSE));"";VLOOKUP(Sheet1!C1;Sheet2!A:E;2;FALSE)))

Do you see the mistake?

/bagus

Post the formula you tried.

Copy it from the formula bar (from excel) and paste it into your
response.

HI Dave,

thanks for the quick reply. I have tried to match your instructions and
have managed only to get #NAME? as a result.

Anything that I am doing wrong?

Product codes contain both letters and numbers, and in addition symbols
(rare). Do I have to format the column to a specific format?

regards,

/bagus

 

[Dave Peterson]

Watch your parentheses:

=IF(ISERROR(VLOOKUP(Sheet1!C1;Sheet2!A:E;2;FALSE));"";
VLOOKUP(Sheet1!C1;Sheet2!A:E;2;FALSE))

I don't see a problem that would cause a #name? error, though. Are you using an
English version of excel?

If not, you'll have to translate =iserror() and =vlookup() to your language.

If you post the language you use, maybe someone can help.

Here is the formula I used:

=IF(ISERROR((VLOOKUP(Sheet1!C1;Sheet2!A:E;2;FALSE));"";VLOOKUP(Sheet1!C1;Sheet2!A:E;2;FALSE)))

Do you see the mistake?

/bagus

Post the formula you tried.

Copy it from the formula bar (from excel) and paste it into your
response.

HI Dave,

thanks for the quick reply. I have tried to match your instructions and
have managed only to get #NAME? as a result.

Anything that I am doing wrong?

Product codes contain both letters and numbers, and in addition symbols
(rare). Do I have to format the column to a specific format?

regards,

/bagus