Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Help With Lookup Formula

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)
    Regards, Duane
    Office2010 in Win7

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,586
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    6 Thread(s)

    Default

    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


  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •