Results 1 to 10 of 10
  1. #1
    Andy_Nel
    Guest

    LOOKUP Problem (2000 9.0.2720)

    I have a LOOKUP problem which is driving me crazy. I created a form which allows our customers to fill in a product number on sheet one. I then use LOOKUP commands in four different cells to automatically fill in infomation about that product (description, cost, etc.) from sheet 2.

    B29 is the input cell of my form. Cells A4 through E7 contain the data I need (sheet 2).

    The formula I use is "=LOOKUP(B29,sheet2!A4:B7)" This will display the product description on my form. I then vary the formula in different cells to display different results.

    "=LOOKUP(B29,sheet2!A4:C7)" displays the unit cost in the next cell on my form, pulling the results from a different column on sheet 2.

    The trouble is when I get to column E. The formula is the same, "=LOOKUP(B2,sheet2!A4:E7)", but in this case it displays the last entry in the A4 column! Is there a limitation to the number of lookups on a worksheet or am I missing something?

    Thanks!

    P.S. - The problem formula is being entered in cell A29 on the attached sample...

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOKUP Problem (2000 9.0.2720)

    I think you should use

    =VLOOKUP(B29;Sheet2!$A$4:$E$7;2;FALSE)

    where the third argument is the column number

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOKUP Problem (2000 9.0.2720)

    Your problem is that the LOOKUP functions uses relation of the number of columns in the table to the number of rows to determine if is should search horizontally or vertically. On your last formula the number of rows exceeded the number of columns, so the search switched from vertical to horizontal.

    You should use the VLOOKUP function instead (I would use if for all of the formulas), like this:

    <pre>=VLOOKUP(B29,Sheet2!A4:E7,2,FALSE)
    =VLOOKUP(B29,Sheet2!A4:E7,3,FALSE)
    =VLOOKUP(B29,Sheet2!A4:E7,4,FALSE)
    =VLOOKUP(B29,Sheet2!A4:E7,5,FALSE)
    </pre>

    Legare Coleman

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: LOOKUP Problem (2000 9.0.2720)

    Consider the VLOOKUP function as it is probably more appropriate to your data. Youu use it as follows,

    <pre>VLOOKUP(Value to find,Range to search, Offset).</pre>

    In your case the values can be supplied by<pre> VLOOKUP(B29,Sheet2!$A$2:$E$7,X,False).</pre>

    where is a number from 2 to 5, depending on which column you want the data from (B,C.D or E). The False parameter is advised if you must have an exact match.

    Ii attach a copy of your workbook with the substituted formula. I also used a Named Range in place of 'Sheet2!$A$2:$E$7' (ProductData).

    Hope that helps you out.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    St. George, Maine, USA
    Posts
    158
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOKUP Problem (2000 9.0.2720)

    Your LOOKUP formula is working as specified.

    Look at HELP for LOOKUP and there is a line:
    'If array covers an area that is wider than it is tall (more columns than rows), LOOKUP searches for lookup_value in the first row.'

    Simply alter formula to: "=LOOKUP(B2,sheet2!A4:E8)",

    Watch it if use use more columns.

    Bob_D

  6. #6
    Andy_Nel
    Guest

    Re: LOOKUP Problem (2000 9.0.2720)

    I'm stunned. I have never solved a problem so quickly! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    Hans - When I pasted your formula into the cell, it returned an error. I am not at the advanced level yet, so maybe I misunderstood you.

    Legare, it worked beautifully. Thanks!

    Thanks to all who replied!

    Andy

  7. #7
    Andy_Nel
    Guest

    Re: LOOKUP Problem (2000 9.0.2720)

    Andrew, your post helped me understand the arguments involved. The M-soft explanations aren't always clear. Thanks!

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: LOOKUP Problem (2000 9.0.2720)

    <P ID="edit"><FONT SIZE=-1>Edited by Andrew Cronnolly on 18-Jul-01 15:22.</FONT></P>edit : I could have sworn I saw a request for this info !

    If you highlight a range, you can give it a name by either using Insert, Name and select Define. Just type whatever name you want into the top line of the dialog box. Or you can type the directly into a dropdown list box situated in the top left beside the Formula Bar.

    If you click on that on the workbook I posted, you should see the Name ProductData, and if you select it it should highlight the range.

    This is a convenient device for using ranges in formulas, as you do not need to bother about using absolute addresses etc.

    Hope that explains it

    Andrew

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOKUP Problem (2000 9.0.2720)

    Sorry Andy, I always forget to mention that my regional settings are different as compared to most of the Excel users on this forum. The reason for the error is simply the fact that I use semicolons to seperate the different arguments. They should be replaced by your list separator (probably a comma).

  10. #10
    Andy_Nel
    Guest

    Re: LOOKUP Problem (2000 9.0.2720)

    You DID see a request for this info. I typed Named Range in Excel Help and didn't see an answer... at first... Then I READ THE HELP FILE a bit farther down and Eureka!...
    <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    I zipped back and deleted the post but you were already helping me.

    Sorry. Thanks Again!

Posting Permissions

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