HOW DO I USE A LOOKUP TABLE???

[paullaup]

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??

 

[jghrist]

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).

 

[PUMPDESIGNER]

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

PUMPDESIGNER

 

[rdas]

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.

 

[onlyadrafter]

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!

 

[mrock]

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.

 

[paullaup]

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

..Paulaup