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
.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 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?
RE: VLOOUP or LOOKUP?
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?
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?
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?
RE: VLOOUP or LOOKUP?
=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?
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?
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.