Results 1 to 10 of 10
Thread: LOOKUP Problem (2000 9.0.2720)

20010718, 13:01 #1Andy_NelGuest
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...

20010718, 13:20 #2
 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

20010718, 13:24 #3
 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

20010718, 13:28 #4
 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.

20010718, 13:33 #5
 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

20010718, 13:47 #6Andy_NelGuest
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

20010718, 13:49 #7Andy_NelGuest
Re: LOOKUP Problem (2000 9.0.2720)
Andrew, your post helped me understand the arguments involved. The Msoft explanations aren't always clear. Thanks!

20010718, 14:22 #8
 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 18Jul01 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

20010718, 14:38 #9
 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).

20010718, 14:46 #10Andy_NelGuest
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!