vertical lookup

[clemkonan]

I have been away for a while need a refresher on how to use a v-look up to return a value but in this case the range of numbers is an interval for example 1-19 = 0, 20-49=0.5, 50-79=1.0, 80-199=1.5, 200-299 =2.0. Dont want to use IF statement because N=7 is the limit.

Thanks

 

[T. Valko]

I have been away for a while need a refresher on how to use a v-look up to return a value but in this case the range of numbers is an interval for example 1-19 = 0, 20-49=0.5, 50-79=1.0, 80-199=1.5, 200-299 =2.0. Dont want to use IF statement because N=7 is the limit.

Thanks

Try this...

Create this 2 column table:

<TABLE style="WIDTH: 68pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=90 border=0 x:str><COLGROUP><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1440" span=2 width=45><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 34pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=45 height=17 x:num>1</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 34pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=45 x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>20</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>50</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>80</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>1.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>200</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>2</TD></TR></TBODY></TABLE>

Let's assume that table is in the range A3:B7.

A1 = some number >=1.

=IF(ISNUMBER(A1),LOOKUP(A1,A3:B7),"")

If A1 is <1 you'll get an error. If A1 >200 the result will be 2.

 

[snoopyhr]

or something like following :

Excel Workbook
	A	B	C
1	Your setup	RV
2	1-19	0
3	20-49	0.5
4	50-79	1
5	80-199	1.5
6	200-299	2
7
8
9		Lookup value	80-199
10		Returned value	1.5
11	OR
12		Lookup value	20-49
13		Returned value	0.5
Sheet3

 

[clemkonan]

Fantastic worked like a charm , I was posting back to explain that I would input a value like 125 and would be seeking an output of 1.5. Worked beautifully.
Thanks again

 

[T. Valko]

Fantastic worked like a charm , I was posting back to explain that I would input a value like 125 and would be seeking an output of 1.5. Worked beautifully.
Thanks again

You're welcome. Thanks for the feedback!