Help With Lookup Formula

Harley

New Member
Joined
Feb 18, 2002
Messages
25
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top