# vertical lookup

#### clemkonan

##### Active Member
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### T. Valko

##### Well-known Member
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

##### Active Member
or something like following :

Excel Workbook
ABC
21-190
320-490.5
450-791
580-1991.5
6200-2992
7
8
9Lookup value80-199
10Returned value1.5
11OR
12Lookup value20-49
13Returned value0.5
Sheet3

#### clemkonan

##### Active Member
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

##### Well-known Member
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!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,624
Messages
5,838,425
Members
430,546
Latest member
CometOz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back