Help With Lookup Formula

[Harley]

I've never used the Lookup function before and I need some help setting this up (I'm assuming that I will be using Lookup).

Worksheet 1, Column A will contain a complete list of approx. 100 commercial titles. Column B will have the corresponding categories for each commercial title.

In Worksheet 2. Column A will be a random list of the commercial titles (all of which are already entered in Worksheet 1, Column A). Rather than manually typing the categories in Column B, I would like to use the lookup function to have these categories entered automatically by referencing the titles and categories in worksheet 1.

When the commercial title in Worksheet 2, Column A matches the commercial title in Worksheet 1, Column A, I would like the category that appears in Worksheet 1, Column B to be automatically entered in Worksheet 2, Column B.

Any suggestions? Thanks.

[Duane]

vlookup is one formula you can fall in love with very quickly!

Basically, the formula looks like this
=vlookup(value,range,column,type)

What it's saying is
"look up this value in that range, when you find it, go over to this column and bring back either exactly what's in it or as close to what's in it as possible."

So, on your first worksheet, in cell B1, you'll enter =VLOOKUP(A1,Sheet2!$A$1:$B$10,2,FALSE) and then drag it down
until it fills every cell in column B that
has a value in column A.

The dollar signs will keep the range the same, while the value A1 will change to A2,
A3, etc, all the way down through the drag.

The 2 means it will count over to the 2nd
column (including the 1st column in which
the matching value is found on your sheet2)...

And false means it will only work on an
exact match. (using true is very handy for
other applications, but more often than not
false is the common type).

Do check your help file for vlookup, it is
a bread'n'butter formula that enjoys widespread application, and you'll discover
all kinds of ways to modify it to meet your reference needs. (for example, the value
variable can be a concatenation of any number of cells, as long as the product is found identically in your range field)

[Aladin Akyurek]

On 2002-03-22 10:35, Harley wrote:
I've never used the Lookup function before and I need some help setting this up (I'm assuming that I will be using Lookup).

Worksheet 1, Column A will contain a complete list of approx. 100 commercial titles. Column B will have the corresponding categories for each commercial title.

In Worksheet 2. Column A will be a random list of the commercial titles (all of which are already entered in Worksheet 1, Column A). Rather than manually typing the categories in Column B, I would like to use the lookup function to have these categories entered automatically by referencing the titles and categories in worksheet 1.

When the commercial title in Worksheet 2, Column A matches the commercial title in Worksheet 1, Column A, I would like the category that appears in Worksheet 1, Column B to be automatically entered in Worksheet 2, Column B.

Any suggestions? Thanks.

Lets say that A2:B100 in Sheet1 houses the commercial titles and their categories (excluding labels in row 1 that you might have).

Select all of the cells in A (excluding the label if you have one in a1), go to the Name Box on the Formula Bar, type Commercials, and hit enter. Now, select all of the cells in A as well as in B (excluding labels) and name it CDATA using the foregoing procedure.

In B2 in Sheet2 enter and drag down as far as needed:

=IF(AND(LEN(A2),COUNTIF(Commercials,A2)),VLOOKUP(A2,CDATA,2,0),"")

Aladin

[Harley]

On 2002-03-22 10:53, Duane wrote:
vlookup is one formula you can fall in love with very quickly!

Basically, the formula looks like this
=vlookup(value,range,column,type)

What it's saying is
"look up this value in that range, when you find it, go over to this column and bring back either exactly what's in it or as close to what's in it as possible."

So, on your first worksheet, in cell B1, you'll enter =VLOOKUP(A1,Sheet2!$A$1:$B$10,2,FALSE) and then drag it down
until it fills every cell in column B that
has a value in column A.

The dollar signs will keep the range the same, while the value A1 will change to A2,
A3, etc, all the way down through the drag.

The 2 means it will count over to the 2nd
column (including the 1st column in which
the matching value is found on your sheet2)...

And false means it will only work on an
exact match. (using true is very handy for
other applications, but more often than not
false is the common type).

Do check your help file for vlookup, it is
a bread'n'butter formula that enjoys widespread application, and you'll discover
all kinds of ways to modify it to meet your reference needs. (for example, the value
variable can be a concatenation of any number of cells, as long as the product is found identically in your range field)

hmmm...the formula works well on most of the cells but on some I get a #N/A error. I've checked the cells numerous times and they are exactly the same text yet I still get this error.

[Mark W.]

hmmm...the formula works well on most of the cells but on some I get a #N/A error. I've checked the cells numerous times and they are exactly the same text yet I still get this error.

Did you check for trailing spaces?

[Duane]

In my example, the $B$2:$B$10 was hypothetical. Make sure you enter the actual range of the Sheet2 information.

In other words, if on Sheet2 you have
data from a1:b350, then enter that in your vlookup with the dollar signs...$A$1:$B$350.