LOOKUP TABLE!? 3

[Housila]

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!

 

[Housila]

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

 

[NormPeterson]

Is the leftmost column sorted in ascending order?

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


Norm

 

[yogia]

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

 

[Housila]

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!

 

[Housila]

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

 

[Housila]

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!

 

[ACtrafficengr]

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

 

[dik]

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

 

[dik]

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

 

[yakpol]

Housila,

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

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

 

[dik]

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

Dik

 

[chichuck]

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

 

[Housila]

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.