Help! I’ve got a table with a list of project values. I want to show which value band each project falls in.

So for a £2,450,000 project it would show in an adjacentcell £0-£3M.

Value bands are 0-3, 3-6, 6-10, 10-15, 15-20, £20M+

I’ve had a play with MATCH and VLOOKUP but can’t get these to work. Pivot tables scare me. Nested if/then/else will be complicated with that many ranges. Any suggestions?

Conditional formatting?

Conditional formatting +1

You can do this with VLOOKUP(). Send me an email, OP and I’ll send you a workbook.

I can’t be arsed to try and explain it here :).

Rob, many thanks for your help. VLOOKUP works a treat!

This way is pretty fast if you are constantly changing the grouping banding values

C1=Lowest Band Value   next band in D1, then E1, F1 & G1 the highest
<table style=”border-collapse: collapse; width: 240pt;” border=”0″ width=”320″ cellspacing=”0″ cellpadding=”0″>
<tbody>
<tr style=”height: 14.4pt;”>
<td class=”xl64″ style=”height: 14.4pt; width: 48pt; font-size: 8pt; color: windowtext; font-family: ‘Arial Narrow’; border: none; background: #63be7b;” align=”right” width=”64″ height=”19″>£1</td>
<td class=”xl64″ style=”width: 48pt; font-size: 8pt; color: windowtext; font-family: ‘Arial Narrow’; border: none; background: #82c77c;” align=”right” width=”64″>£1,000</td>
<td class=”xl64″ style=”width: 48pt; font-size: 8pt; color: windowtext; font-family: ‘Arial Narrow’; border: none; background: #ffeb84;” align=”right” width=”64″>£5,000</td>
<td class=”xl64″ style=”width: 48pt; font-size: 8pt; color: windowtext; font-family: ‘Arial Narrow’; border: none; background: #fed480;” align=”right” width=”64″>£50,000</td>
<td class=”xl64″ style=”width: 48pt; font-size: 8pt; color: windowtext; font-family: ‘Arial Narrow’; border: none; background: #f8696b;” align=”right” width=”64″>£250,000</td>
</tr>
</tbody>
</table>
Table of values column C3

and copy in

=IF(C3>C$1,IF(C3>D$1,IF(C3>E$1,IF(C3>E$1,IF(C3>F$1,IF(C3>G$1,”£”&TEXT(G$1,”#,##0″)&”+”,”£”&TEXT(F$1,”#,##0″)&”>£”&TEXT(G$1,”#,##0″)),”£”&TEXT(E$1,”#,##0″)&”>£”&TEXT(F$1,”#,##0″)),”£”&TEXT(D$1,”#,##0″)&”+”),”£”&TEXT(D$1,”#,##0″)&”>£”&TEXT(E$1,”#,##0″)),”£”&TEXT(C$1,”#,##0″)&”>£”&TEXT(D$1,”#,##0″)),”£000>£”&TEXT(C$1,”#,##0″))

This will produce its own labels against each value in column C

Rather than nested if blocks etc you could just use the IFS function which takes multiple conditions and returns the value of the first one that’s true.