×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

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!

*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

HOW DO I USE A LOOKUP TABLE???

HOW DO I USE A LOOKUP TABLE???

HOW DO I USE A LOOKUP TABLE???

(OP)
I've heard about lookup tables and people say they are very powerfull. But I haven't a clue how they work or how to write the syntax??
I want to select a beam size from a drop down box on one sheet. When I select that beam size, I want certain cells to return the properties of the beam.  I have tabulated the properties of a few beams on another spreadsheet but dont know how to make the cells reference them?

I suppose I could use "if" statements, but I think that would take forever!!

Any ideas??

RE: HOW DO I USE A LOOKUP TABLE???

Basically, you enter the range of the table of properties (with the size either in the first row or the first column), the cell with the size, and the number of the column or row that contains the property you are after into the VLOOKUP or HLOOKUP function.  See Excel Help for details.  You have a choice to look up the exact size or an approximate size (if the table is ordered).

RE: HOW DO I USE A LOOKUP TABLE???

Just select the cell with the lookup and then observe what is happening with the Trace Precedents and Dependants functions.

PUMPDESIGNER

RE: HOW DO I USE A LOOKUP TABLE???

Hi paulaup.
If the range of choice of the beam section is 29 or below, you can use the "Choose" function. Please go through the excel help files on the syntax of this function.
You will be using the List box for selecting the desired beam sections. Necessarily you will be giving the cell reference to link the description to tthe sequence no. use this cell reference and the data sheet cell reference to display the results in your desired location.

RE: HOW DO I USE A LOOKUP TABLE???

Hello,

A Lookup table works like this

     A      B     C     D
1  Beam #   X     Y     Z
2  Beam 1   1     2     3
3  Beam 2   4     5     6
4  Beam 3   7     8     9

You enter in a cell, or calculate the result e.g. in E1 you will have Beam 2,

In F1 enter the formula

=VLOOKUP($E$1,$A$1:$D$4,2)

where $E$1 in the result Cell i.e. Beam 2

$A$1:$D$3 is the table of results

and 2 is the column from which an answer is required i.e. in this case 4.

HLOOKUP works in a similar way but gets answers from a specific row.
e.g. in E2 enter B
in F2 enter this formula

=HLOOKUP($E$2,$B$1:$D$4,4)

you will get the answer 8.

These lookups results can vary, I suggest you read the help files, this will explain it better than I can.


----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

RE: HOW DO I USE A LOOKUP TABLE???

Hi Paullaup,

Here is my way of doing this (and I use it a lot).

Just to mention: I don't like LOOKUP, VLOOKUP and HLOOKUP. I prefer combining INDEX with MATCH. I had a lot of trouble with LOOKUP (wrong results), in the MATCH command you can define better what happens if you donot find the value in your table.

But let me explain you how I would work with your beam table (using the example of the other post):

     A          B     C     D
1  Beam #   X     Y     Z
2  Beam 1   1     2     3
3  Beam 2   4     5     6
4  Beam 3   7     8     9


1) Create a pulldown box. Be aware that there are two types of pulldowns. Use the toolbox for forms, not fot controls (as I use Excel in other language, I'm not sure wether the name is correct, I'll do my best)

2) Define the input range to A2:A4, chose any cell to be linked to that pulldown button. I normally use the cell wich will be hidden by the pulldown, nobody needs to see this value. Lets say its $M$5.

3) To get the value for X of your table for the selected beam, enter =INDEX(C2:C4;M5)

I think this is a easy way to do what you ased for, hope I made it clear.

RE: HOW DO I USE A LOOKUP TABLE???

(OP)
Thanks all, As usual, you have been a great help.

..Paulaup

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! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close