# Thread: LOOKUP Problem (2000 9.0.2720)

1. ## 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. ## 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. ## 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>

4. ## 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.

5. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•