LOOKUP in table

[natajime80]

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.

 

[Denial]

You might need to do an additional check for an EXACT match. If there is an exact match, use the value your present approach yields. If there is not an exact match, use the next result down the table. Some sort of special treatment will probably be required for the bottom entry in the table.

 

[MintJulep]

Perhaps ROUND(), or one of its derriviatives will do what you want.

 

[joerd]

You'll need a combination of match and index, so that you can add 1 to the match found.
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: [tt]=INDEX($B$1:$B$4,MATCH($C$1,$A$1:$A$4,1)+ISNA(MATCH($C$1,$A$1:$A$4,0)))[/tt]
The second part: [tt]=ISNA(MATCH($C$1,$A$1:$A$4,0))[/tt] 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.

 

[BigInch]

"Fix" rounds up.

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

 

[bltseattle]

If your "lookup vector" has nice even increments like you've shown in your example (1, 2, 3, 4) you could use the CEILING function, then perform the LOOKUP. CEILING will increase the value to the next increment.

=LOOKUP(CEILING(value,increment),lookupvector, resultvector)

 

[BigInch]

Did I mix up and wrote the VB "Fix" function rather than the VBA "Ceiling" function?

BigInch-born in the trenches.

http://virtualpipeline.spaces.msn.com

 

[natajime80]

Thank you all for your responses. I have thought about the ceiling function but the nominal values do not have constant increments. I made my example too simple. It would look more like:

Calc value = 2.5

Nominal value Size
1.8 A
3.2 B
4.5 C
4.7 D
5.1 E

 

[BigInch]

in any case, the double lookup on an index column will work.

BigInch-born in the trenches.

http://virtualpipeline.spaces.msn.com

 

[chichuck]

natajime80,

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

 

[kbarnett]

You could try shifting the values in the result column up one row, as in:

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

 

[natajime80]

Thank you all for your posts. BigInch, your method works very nicely. Thanks.

 

[cummings54]

A little outside the box thinking here...
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)