VLOOUP or LOOKUP?

[jgreg43]

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

 

[bltseattle]

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

 

[jgreg43]

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

 

[jgreg43]

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.

 

[bltseattle]

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)

 

[jgreg43]

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.

 

[jgreg43]

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

 

[jgreg43]

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.

 

[bltseattle]

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

 

[jgreg43]

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.