vlook up help

[lucki_87]

<table border="0" cellpadding="0" cellspacing="0" width="339"><col style="mso-width-source:userset;mso-width-alt:4132;width:85pt" width="113"> <col style="mso-width-source:userset;mso-width-alt:5376;width:110pt" width="147"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;width:85pt" height="17" width="113">Look Up Table

Turnover band</td> <td class="xl68" style="width:110pt" width="147">Turnover</td> <td class="xl69" style="width:59pt" width="79">Title</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">N</td> <td class="xl70">35,000</td> <td class="xl71">32500-35000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">M</td> <td class="xl70">32,500</td> <td class="xl71">30000-32500</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">L</td> <td class="xl70">30,000</td> <td class="xl71">27500-30000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">K</td> <td class="xl70">27,500</td> <td class="xl71">25000-27500</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">J</td> <td class="xl70">25,000</td> <td class="xl71">22500-25000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">I</td> <td class="xl70">22,500</td> <td class="xl71">20000-22500</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">H</td> <td class="xl70">20,000</td> <td class="xl71">17500-20000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">G</td> <td class="xl70">17,500</td> <td class="xl71">15000-17500</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">F</td> <td class="xl70">15,000</td> <td class="xl71">12500-15000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">E</td> <td class="xl70">12,500</td> <td class="xl71">10000-12500</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">D</td> <td class="xl70">10,000</td> <td class="xl71">7500-10000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">C</td> <td class="xl70">7,500</td> <td class="xl71">5000-7500</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">B</td> <td class="xl70">5,000</td> <td class="xl71">2500-5000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">A</td> <td class="xl72">2,500</td> <td class="xl73">0-2500</td> </tr> </tbody></table>
<table border="0" cellpadding="0" cellspacing="0" width="64"><col style="width:48pt" width="64"> <tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">Turnover</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">8000</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">3700</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">5000</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">4000</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">34000</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2500</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">3800</td> </tr> </tbody></table>
=VLOOKUP(C2,'Lookup tables'!$A$32:$C$45,2)

i want it to return the title
but it kept giving me n/a

 

[tc88]

<TABLE border=0 cellSpacing=0 cellPadding=0 width=339><COLGROUP><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 85pt; HEIGHT: 12.75pt" class=xl67 height=17 width=113>Look Up Table

Turnover band
</TD><TD style="WIDTH: 110pt" class=xl68 width=147>Turnover</TD><TD style="WIDTH: 59pt" class=xl69 width=79>Title</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl65 height=17>N</TD><TD class=xl70>35,000</TD><TD class=xl71>32500-35000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl65 height=17>M</TD><TD class=xl70>32,500</TD><TD class=xl71>30000-32500</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl65 height=17>L</TD><TD class=xl70>30,000</TD><TD class=xl71>27500-30000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl65 height=17>K</TD><TD class=xl70>27,500</TD><TD class=xl71>25000-27500</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl65 height=17>J</TD><TD class=xl70>25,000</TD><TD class=xl71>22500-25000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl65 height=17>I</TD><TD class=xl70>22,500</TD><TD class=xl71>20000-22500</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl65 height=17>H</TD><TD class=xl70>20,000</TD><TD class=xl71>17500-20000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl65 height=17>G</TD><TD class=xl70>17,500</TD><TD class=xl71>15000-17500</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl65 height=17>F</TD><TD class=xl70>15,000</TD><TD class=xl71>12500-15000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl65 height=17>E</TD><TD class=xl70>12,500</TD><TD class=xl71>10000-12500</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl65 height=17>D</TD><TD class=xl70>10,000</TD><TD class=xl71>7500-10000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl65 height=17>C</TD><TD class=xl70>7,500</TD><TD class=xl71>5000-7500</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl65 height=17>B</TD><TD class=xl70>5,000</TD><TD class=xl71>2500-5000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl66 height=17>A</TD><TD class=xl72>2,500</TD><TD class=xl73>0-2500</TD></TR></TBODY></TABLE>
<TABLE border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 48pt; HEIGHT: 12.75pt" height=17 width=64>Turnover</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17 align=right>8000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17 align=right>3700</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17 align=right>5000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17 align=right>4000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17 align=right>34000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17 align=right>2500</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17 align=right>3800</TD></TR></TBODY></TABLE>
=VLOOKUP(C2,'Lookup tables'!$A$32:$C$45,2)

i want it to return the title
but it kept giving me n/a

The lookup value (C2 in your formula) needs to be in the left-most column of your table array ('Lookup tables'!$A$32:$C$45). The "2" in your formula is the argument that tells Excel which column value to return, and TRUE or FALSE tells it to return an approximate or exact match, respectively. I usually use FALSE.

 

[lucki_87]

the c2 value is from the turnover colum respectively the 8000

 

[Jonmo1]

Your data needs to be sorted ASCENDING on the Turnover column
A 2,500 0-2500
B 5,000 2500-5000
Etc..

Then your formula is
=VLOOKUP(C2,'Lookup tables'!$B$32:$C$45,2)

Hope that helps.

 

[kmack940]

=VLOOKUP(C2,'Lookup tables'!$A$32:$C$45,2)

did you try declairing if you want an exact match? Example:

=VLOOKUP(C2,'Lookup tables'!$A$32:$C$45,2,false)

 

[lucki_87]

Your data needs to be sorted ASCENDING on the Turnover column
A 2,500 0-2500
B 5,000 2500-5000
Etc..

Then your formula is
=VLOOKUP(C2,'Lookup tables'!$B$32:$C$45,2)

Hope that helps.

awesome that work great

 

[lucki_87]

<table border="0" cellpadding="0" cellspacing="0" width="339"><colgroup><col style="mso-width-source:userset;mso-width-alt:4132;width:85pt" width="113"> <col style="mso-width-source:userset;mso-width-alt:5376;width:110pt" width="147"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;width:85pt" height="17" width="113">Look Up Table

Turnover band</td> <td class="xl68" style="width:110pt" width="147">Turnover</td> <td class="xl69" style="width:59pt" width="79">Title</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">N</td> <td class="xl70">35,000</td> <td class="xl71">32500-35000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">M</td> <td class="xl70">32,500</td> <td class="xl71">30000-32500</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">L</td> <td class="xl70">30,000</td> <td class="xl71">27500-30000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">K</td> <td class="xl70">27,500</td> <td class="xl71">25000-27500</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">J</td> <td class="xl70">25,000</td> <td class="xl71">22500-25000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">I</td> <td class="xl70">22,500</td> <td class="xl71">20000-22500</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">H</td> <td class="xl70">20,000</td> <td class="xl71">17500-20000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">G</td> <td class="xl70">17,500</td> <td class="xl71">15000-17500</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">F</td> <td class="xl70">15,000</td> <td class="xl71">12500-15000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">E</td> <td class="xl70">12,500</td> <td class="xl71">10000-12500</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">D</td> <td class="xl70">10,000</td> <td class="xl71">7500-10000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">C</td> <td class="xl70">7,500</td> <td class="xl71">5000-7500</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt" height="17">B</td> <td class="xl70">5,000</td> <td class="xl71">2500-5000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">A</td> <td class="xl72">2,500</td> <td class="xl73">0-2500</td> </tr> </tbody></table>

is there a formula to sort this in accending order?

 

[Jonmo1]

If sorting your data is not an option, try

=INDEX($C$32:$C$45,MATCH(CEILING(C2,2500),$B$32:$B$45,0))

Excel Workbook
	C	D
1	Turnover	Formula
2	8000	7500-10000
3	3700	2500-5000
4	5000	2500-5000
5	4000	2500-5000
6	34000	32500-35000
7	2500	0-2500
8	3800	2500-5000
Sheet1

Excel Workbook
	A	B	C
31	Turnover Brand	Turnover	Title
32	N	35000	32500-35000
33	M	32500	30000-32500
34	L	30000	27500-30000
35	K	27500	25000-27500
36	J	25000	22500-25000
37	I	22500	20000-22500
38	H	20000	17500-20000
39	G	17500	15000-17500
40	F	15000	12500-15000
41	E	12500	10000-12500
42	D	10000	7500-10000
43	C	7500	5000-7500
44	B	5000	2500-5000
45	A	2500	0-2500
Sheet1

 

[lucki_87]

is there a way to change this to a vba code?