×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

LOOKUP in table

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.

RE: LOOKUP in table

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.

RE: LOOKUP in table

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

RE: LOOKUP in table

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

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

BigInchworm-born in the trenches.
http://virtualpipeline.spaces.msn.com

RE: LOOKUP in table

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)

RE: LOOKUP in table

(OP)
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              

RE: LOOKUP in table

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



RE: LOOKUP in table

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

RE: LOOKUP in table

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

RE: LOOKUP in table

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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources