×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

LOOKUP TABLE!?
3

LOOKUP TABLE!?

LOOKUP TABLE!?

(OP)
I have been using look up table a lot and been trying it with numbers, alphabets, and alphanumeric characters and it seemed to work all good. However, I don't know for what reason, when I'm trying a simple lookup function as follows, its giving me the value at the end of the row, not the corresponding row value.

MATERIAL = A36 ( THIS IS THE VARIABLE)

COL A      COL B
A36        30000
A242       32000
304 SS     30000
ETC...       ETC....


NO matter how I form the look up function (with or without $ signs), it always gives the value corresponding to the last row.

Please help!


RE: LOOKUP TABLE!?

(OP)
PS: instead of giving value 30,000 that corresponds to A36 it will give me the value of the last cell ( here etc. or whatever number I put there). When I just have one row to lookup from it gives the right value...haha

RE: LOOKUP TABLE!?


Is the leftmost column sorted in ascending order?

Does your LOOKUP value cell contain the whole string MATERIAL = A36
or just A36?


Norm

RE: LOOKUP TABLE!?


HI Housila:

The reason you are not getting the right result is because ...
Use of LOOKUP function requires that the values in the LookUp column be arranged in ascending order

One of the ways would be use the VLOOKUP function as in ...

thread770-196234: LOOKUP TABLE!?.gif" border="0">

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com

RE: LOOKUP TABLE!?

(OP)
I just have those two columns. I did try putting one more column, 1,2,3 and its still not doing any good, now its even pulling more weired numbers something in between.
Does it have to do with the bug in 2007 version?

here is what i'm trying now

value    gasket c    
        
1            gasket a    20
2            gasket b    50
3            gasket cdcd    40
4            gasket c    25
=LOOKUP($B$1,B3:B6,A3:A6)    =LOOKUP($B$1,B3:B6,C3:C6)

its giving values 2 and 50 instead of 4 and 25!

RE: LOOKUP TABLE!?

(OP)
Yogia,

I did try something like that earlier, and it worked just once, when i tested it with other values of materials, it gave weired numbers again. let me try it again though.

Thanks

RE: LOOKUP TABLE!?

(OP)
Thanks everyone. I think I know what I was missing. I was missing the =VLOOKUP(D3,A5:B8,2,0) "0" part at the end.

It seem to work fine now. Thanks a ton!

RE: LOOKUP TABLE!?

Don't forget that, since it is a text field, A242 is LESS than A36!

     "...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail." - Wm. Phelps Eno, ca. 1928

"I'm searching for the questions, so my answers will make sense." - Stephen Brust

RE: LOOKUP TABLE!?

I generally have my table of data based on a usual display of sizes,

for example, bolt diametres ranging from 3/8" to 1-1/2" by increments of 1/32 of an inch.  From this I use formulae to calculate the diametre, area, effective tension area, etc.

I then format the calculated data and use paste special to convert the data from formulae to numbers

I insert a column on the left and number these from 1 to however many lines of data again using the first cell as 1 and then using a formula = previous cell + 1.

I insert another column on the left (before the sequential numbers) and copy the bolt sizes (column 3) to it.  I use this column to establish my drop down list order.

I sort columns 2 to whatever based on the ascending order of column 3; this becomes my vlookup table.

I then use vlookup to find the data from the cell containing the dropdown list data.

Column 2 numbers are likely no longer sequential, but can be used to sort the data to it's origial form.

Dik

RE: LOOKUP TABLE!?

I should have added that six months ago, I couldn't get vlookup to work, either... but was prompted by info from this forum...

Dik

RE: LOOKUP TABLE!?

Housila,

You may try using combination of MATCH and INDEX functions instead of LOOKUP. In the case of your first posting:

CODE

= INDEX(B4:B7,MATCH(B1,A4:A7,0))

Setting the last argument in MATCH function to "0" makes it non-sensitive to the order of A4:A7 array.

RE: LOOKUP TABLE!?

The problem with this forum is not enough 'yak'... <G> Thanks for the added info...

Dik

RE: LOOKUP TABLE!?

yogi has it half correct.  The last parameter in the vlookup function is either 0 or 1. 1 is the default, and if the parameter is omitted, the function assumes a value of 1.  When set to zero as Housila has done, flags the function to specify an exact match for the lookup.  For this case, the table need not be in ascending order.  If 1 is specified, or if the parameter is omitted, the function looks for the largest value that is less than or equal to the lookup value.  For this case, the table needs to be in ascending order.

regards,

chichuck

RE: LOOKUP TABLE!?

(OP)
Thanks chikchuck. The lookup table with exact or approx match works very well with version 07 now. However, I couldn't use it for looking up data with multiple arrays, so had to remove the "0" from the command. It works well though, and gives me the exact match for all the columns every time even though my first column is set with "0" parameter.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources