Vlookups and ranges

[CaseyofKent]

Hi,

I know this question has probably been posted 100 times before but here it is again.

I have a tab called Lookups which contains the following table:

<TABLE style="WIDTH: 154pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=205 border=0><COLGROUP><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 3555" width=125><COL style="WIDTH: 60pt" width=80><TBODY><TR style="HEIGHT: 21pt" height=28><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 94pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 21pt; BACKGROUND-COLOR: transparent" width=125 height=28>Band</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=80>Factor</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>0-9999</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1.1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>10000 - 19999</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1.2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>20000-29999</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1.3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>30000-39999</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1.4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>40000-49999</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0.5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>50000-59999</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0.6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>60000-69999</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0.7</TD></TR></TBODY></TABLE>

On a seperate tab I need a formula that returns the correct factor in cell B1 based on the value of cell A1.

So, if cell A1 contains the number 24355 - in cell b1 I want the value 1.3 to appear.

Is there a way to do this without using VBA? I am desperate to avoid VBA. Im quite prepared to change the layout of the table if it makes life easier. I'm after a simple solution if there is one.

I would consider myself very much an amateur at Excel, and the more posts I read the more evident it seems!

Thanks

 

[VoG]

Try changing your table like this

Excel Workbook
	A	B	C	D	E
1	0	1.1		24355	1.3
2	10000	1.2
3	20000	1.3
4	30000	1.4
5	40000	0.5
6	50000	0.6
7	60000	0.7
Sheet4

 

[WinteE]

Split column "Band" in two -> From (col A) and To (col B). "Factor" is column C.

=SUMPRODUCT((A2:A8<=24355)*(B2:B8>=24355)*(C2:C8))

 

[CaseyofKent]

VoG - Thanks for the reply.

I don't know if that formula will work. I think I have to leave the numbers as they are. For 60,000 I need it to return the value 0.7 but it returns 0.6. I guess because 59999 is closer

<TABLE style="WIDTH: 390pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=516 border=0><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2446" span=6 width=86><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #cacaca" width=86 height=21>


</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0 1.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #f0f0f0 1.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: #f0f0f0 1.5pt solid; BACKGROUND-COLOR: #cacaca" width=86>A</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0 1.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 65pt; BORDER-BOTTOM: #f0f0f0 1.5pt solid; BACKGROUND-COLOR: #cacaca" width=86>B</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0 1.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 65pt; BORDER-BOTTOM: #f0f0f0 1.5pt solid; BACKGROUND-COLOR: #cacaca" width=86>C</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0 1.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 65pt; BORDER-BOTTOM: #f0f0f0 1.5pt solid; BACKGROUND-COLOR: #cacaca" width=86>D</TD><TD class=xl74 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 65pt; BORDER-BOTTOM: #f0f0f0 1.5pt solid; BACKGROUND-COLOR: #cacaca" width=86>E</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl70 style="BORDER-RIGHT: #f0f0f0 0.5pt solid; BORDER-TOP: #f0f0f0 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 65pt; BORDER-BOTTOM: #f0f0f0 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #cacaca" width=86 height=21>1</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86>0</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86>1.1</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86>60000</TD><TD class=xl75 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86>0.6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 65pt; BORDER-BOTTOM: #f0f0f0 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #cacaca" width=86 height=20>2</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86>19999</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86>1.2</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD><TD class=xl76 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 65pt; BORDER-BOTTOM: #f0f0f0 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #cacaca" width=86 height=20>3</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86>29999</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86>1.3</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD><TD class=xl76 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 65pt; BORDER-BOTTOM: #f0f0f0 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #cacaca" width=86 height=20>4</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86>39999</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86>1.4</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD><TD class=xl76 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 65pt; BORDER-BOTTOM: #f0f0f0 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #cacaca" width=86 height=20>5</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86>49999</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86>0.5</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD><TD class=xl76 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 65pt; BORDER-BOTTOM: #f0f0f0 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #cacaca" width=86 height=20>6</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86>59999</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86>0.6</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD><TD class=xl76 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=86></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl71 style="BORDER-RIGHT: #f0f0f0 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 65pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #cacaca" width=86 height=21>7</TD><TD class=xl72 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=86>69999</TD><TD class=xl72 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=86>0.7</TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=86></TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=86></TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 65pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=86>


</TD></TR></TBODY></TABLE>

 

[VoG]

You need to use the values that I posted

Excel Workbook
	A	B	C	D	E
1	0	1.1		60000	0.7
2	10000	1.2
3	20000	1.3
4	30000	1.4
5	40000	0.5
6	50000	0.6
7	60000	0.7
Sheet4

 

[CaseyofKent]

VoG - I noticed that it works lovely when I use your values but not when I used mine. How odd! I would much rather do it your way, but doing things simply isn't always the foremost concern of my colleagues!!

WinteE - I have split my table and its done the trick. Thanks very much for your help.