×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

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!

*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

VLOOUP or LOOKUP?

VLOOUP or LOOKUP?

VLOOUP or LOOKUP?

(OP)
I am trying to recall how to lookup a cell within a range. But my ranges do not cover entire range. See example below

     .5   .25   .50   .75   1.00   1.25   1.50

50   .04
100  .16
125  .25
150  .33  .04
175  .48  .06
750       1.02  .24
1000      1.80  .42   .13    .05
2000                                 .67  .22

this is the abbreviated table.

I want to provide the # on the left and the # at the top to find the number in the range. Which would be best for this and how do I do the formula? any help would be appreciated.

Thanks

RE: VLOOUP or LOOKUP?

Your column headers are in B1:H1
Your row headers are in A2:A9

MATCH(your_top_num,B1:H1,true) will return the column which corresponds
MATCH(your_left_num,A2:A9,true) will return the row which corresponds
Change true to false if you will only accept exact matches

Now to put it together

your_looked_up_value = OFFSET($a$1,MATCH(your_left_num,A2:A9,true),MATCH(your_top_num,B1:H1,true),1,1)

I suggest looking up OFFSET in help to see how it works...

RE: VLOOUP or LOOKUP?

(OP)
Thanks, I never considered OFFSET before, I will check it out further.

RE: VLOOUP or LOOKUP?

(OP)
I just tried the formula and I am now getting a #N/A when I change TRUE to FALSE and 0.00 value when TRUE where I should see .16

I think one thing I forgot to mention is I need it to roundup to next number on list.

How do I do that? I know I need to input the ROUNDUP into the formula but have no idea where in the formula it should go.

RE: VLOOUP or LOOKUP?

MATCH returns the position of the item in the array, so adding 1 to the MATCH return is like rounding up.  Try using:

 OFFSET($a$1,MATCH(your_left_num,A2:A9,true)+1,MATCH(your_top_num,B1:H1,true),1,1+1)

RE: VLOOUP or LOOKUP?

(OP)
here is my formula and I am getting a #VALUE indication now.
I am still reading and trying to understand what the problem is but can not find the problem. I keep trying what you provide but still no luck. I think it may have something to do with Reference.

RE: VLOOUP or LOOKUP?

(OP)
=OFFSET($AP$1,MATCH(A15,AO8:AO34,TRUE)+1,MATCH(A16,AP7:AX7,TRUE),1,1+1)

RE: VLOOUP or LOOKUP?

(OP)
I found a formula that has brought me a little closer, but I seem to only get 1 cell and not the next in the list.

=INDEX(AP8:AX34,MATCH(A15,AO8:AO34,TRUE),MATCH(A16,AP7:AX7,TRUE))

even if I change TRUE to FALSE it does not go down the line to next number.

RE: VLOOUP or LOOKUP?

Oops, my mistake earlier I should have used:

OFFSET($a$1,MATCH(your_left_num,A2:A9,true)+1,MATCH(your_top_num,B1:H1,true)+1,1,1)

If INDEX is mostly working for you, play with adding the "+1" after the MATCH(..) statements:

=INDEX(AP8:AX34,MATCH(A15,AO8:AO34,TRUE)+1,MATCH(A16,AP7:AX7,TRUE)+1)
 
 
 

If this doesn't work and you want to send me a copy I will try to take a look...

bltseattle@care2.com

RE: VLOOUP or LOOKUP?

(OP)
It seems that INDEX is automatically rounding up. The formula appears to work for what I need at this time.

here is the formula:

=INDEX(AP8:AX34,MATCH(A15,AO8:AO34,TRUE),MATCH(A16,AP7:AX7,TRUE))


Thanks for getting me in the right area.

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! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close