LOOKUP in table
LOOKUP in table
(OP)
Hello:
I have a program that will give me a calculated value , and a table that lists nominal values vs. size. I need the ability to take my calculated value and find the appropiate size. However the LOOKUP function will give me the next smallest value, and what I need is the next higher..
I'll try to illustrate my question with a made up example:
Calc value= 2.5
Nominal Value Size
1 A
2 B
3 C
4 D
Right now with LOOKUP it will give a size of B, but what I need is a size C.
I hope somebeody can give me a hint in the right direction.
I have a program that will give me a calculated value , and a table that lists nominal values vs. size. I need the ability to take my calculated value and find the appropiate size. However the LOOKUP function will give me the next smallest value, and what I need is the next higher..
I'll try to illustrate my question with a made up example:
Calc value= 2.5
Nominal Value Size
1 A
2 B
3 C
4 D
Right now with LOOKUP it will give a size of B, but what I need is a size C.
I hope somebeody can give me a hint in the right direction.





RE: LOOKUP in table
RE: LOOKUP in table
RE: LOOKUP in table
For example, assuming the first column is in A1:A4 and the second column is in B1:B4, and the lookup value is in C1: =INDEX($B$1:$B$4,MATCH($C$1,$A$1:$A$4,1)+ISNA(MATCH($C$1,$A$1:$A$4,0)))
The second part: =ISNA(MATCH($C$1,$A$1:$A$4,0)) gives TRUE (= 1) if there is not an exact match for $C$1 in the range $A$1:$A$4, else it gives 0.
Purists may not agree with this approach, but in Excel it works. You could also fit it into an IF statement, if you like that better.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: LOOKUP in table
Or use a double lookup,
Add an index column to your nominal value column, then have your lookup return the index.
Add 1 to index and lookup that number in the index and get the return column item.
idx value return
1 2.1 3.1416
2 7.8 1.4142
3 .95 0.6
4 6 1E+23
5 2.1 30000
I think this will do it.
=Lookup(Lookup(7.9, V2:V5, I2:I5)+1, I2:I6, R2:R6)
BigInch
-born in the trenches.
http://virtualpipeline.spaces.msn.com
RE: LOOKUP in table
=LOOKUP(CEILING(value,increment),lookupvector, resultvector)
RE: LOOKUP in table
BigInch
-born in the trenches.
http://virtualpipeline.spaces.msn.com
RE: LOOKUP in table
Calc value = 2.5
Nominal value Size
1.8 A
3.2 B
4.5 C
4.7 D
5.1 E
RE: LOOKUP in table
BigInch
-born in the trenches.
http://virtualpipeline.spaces.msn.com
RE: LOOKUP in table
try this variation:
cell value = 2.5
| A B C Columns
-------------------
1 | 0 1 A
2 | 1 2 B
3 | 2 3 C
4 | 3 4 D
5 | 4 5 E
for your lookup, use this formula:
vlookup(cell value, a1:c5, 3) Note: make sure that the flag for exact match is off, that is the default
This will give a zero if you have a cell value > 5.
I use this extensively when I am trying to interpolate values I've looked up from a table, it gives me an upper limit to use in interpolation calculations.
Hope this helps
regards,
chichuck
RE: LOOKUP in table
index result
0 a
1.8 b
3.2 c
4.5 d
4.7 e
5.1 (out of range)
Use =vlookup(value,i2:r6,2)
I added the 0 to the first column so the default would be the smallest result, "a", for any value less than the smallest index. The only problem with this is that if you want an exact match to the index number to return the result your previous table had, then you would either have to modify the formula to subtract a non-significant amount from "value" before using it (replace "value" in the equation with "value-.001"), or alter the index numbers to be slightly larger than the exact amount you want referenced (replace 1.8 with 1.8001, replace 3.2 with 3.2001, etc.).
RE: LOOKUP in table
RE: LOOKUP in table
Why not sort your table in decending order
4 D
3 C
2 B
1 A
then all you need is
=index(letter_range,Match(val,numb_range,-1)1)