×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# 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?

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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!