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!
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!?
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 ...
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: LOOKUP TABLE!?
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!?
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!?
It seem to work fine now. Thanks a ton!
RE: LOOKUP TABLE!?
"...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!?
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!?
Dik
RE: LOOKUP TABLE!?
You may try using combination of MATCH and INDEX functions instead of LOOKUP. In the case of your first posting:
CODE
Setting the last argument in MATCH function to "0" makes it non-sensitive to the order of A4:A7 array.
RE: LOOKUP TABLE!?
Dik
RE: LOOKUP TABLE!?
regards,
chichuck
RE: LOOKUP TABLE!?