Product Range

[lordtopcat]

Hi Guys,

Now I have a sheet of about 800 products that is set up like this:

A1.......................A2
111.....................Product A
222.....................Product B

Now what I want to do in a separate sheet, is make it so that when someone enters the item code, for example 111, then in the box next to it the name of the product automatically appears.

I've achieved this with the if statement for a single product =IF(A1=111,"Product A",) but I have over 800 products and was wondering if there was a simple way of doing what I would like to achieve.

Cheers.

[Paul]

Hi Lordtopcat,

You should use VLOOKUP for this.

If you enter 111 into cell A1 on Sheet2, then in B1 use:

=VLOOKUP(A1,Sheet1!$A$1:$B$800,2,0)

Adjust the 800 to your actual last row on Sheet1. Hope that helps!

[lordtopcat]

Thanks Paul, that's great!

I've had a mess around and it displays #/NA when there is nothing entered, can I change this?

Cheers.

[Paul]

In 2007+ you can use:

=IFERROR(VLOOKUP(A1,Sheet1!$A$1:$B$800,2,0),"")

For compatibility with 2003 and earlier, use:

=IF(ISNA(VLOOKUP(A1,Sheet1!$A$1:$B$800,2,0)),"",VLOOKUP(A1,Sheet1!$A$1:$B$800,2,0))

[lordtopcat]

Thanks Paul!