Using approximate match vlookup with named range string (?) as table array?

[reagent]

Hi, I've been banging my head against the desk all week, some help would be a lifesaver.

I've got the following reference table with different 'bands', each of which is set up as a separate defined name range. So where BandNo = 1 (cells A2:C4) is called Band1, BandNo = 2 is called Band2 etc.

[TABLE="width: 489"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]BandNo[/TD]
[TD]BreakVolume[/TD]
[TD]Discount[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]6[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]23[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]24[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]7[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8[/TD]
[TD]70[/TD]
[/TR]
</tbody>[/TABLE]

Now I need to perform a close-match vlookup in my data table against this reference table. My data table is similar to this;

[TABLE="width: 651"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Quantity[/TD]
[TD]BandNo[/TD]
[TD]Range (concatenated text field)[/TD]
[TD]Desired Result (discount)[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1[/TD]
[TD]Band1[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]Band3[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]Band3[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]2[/TD]
[TD]Band2[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]

Now the following formula works if I manually type out the defined name within the formula, VLOOKUP(A2,Band1,3,TRUE) - however I can't find a way to reference my concatenated Range field. Basically I need to look up the named range within the vlookup.

Any ideas?

 

[reagent]

Slight error in this - the defined name is set up like Band1 (cells B2:C4)

The VLOOKUP is therefore =VLOOKUP(A2,Band1,2,TRUE) which gives the 85.

However I can't use =VLOOKUP(A2,C2,2,TRUE)[FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif] as I'm getting #n/a - obviously it's not recognising my concatenated string as a defined name.[/FONT]

[FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]Is there any way to fix my formula?[/FONT]

 

[reagent]

Just found the answer, annoying as it's a really simple fix.

INDIRECT converts a cell to a reference, so this gives me the correct result...

=VLOOKUP(A20,INDIRECT(C20),2,TRUE)