Complex Look Up

[x168]

I have a cell that does a lookup

I now need to add a facility that alters the results depending on a number.
For example:

I have it as
=LOOKUP(I6,{0,10,20,30},{"N","BV","AV","HD"})

I now need the results to change depending on the number I put in a different cell. For example if I put in:

4 (I need it to give me N, BV, AV or HD)
3 (it gives me N.75, BV.75, AV.75 or HD.75)
2 (it gives me N.5, BV.5, AV.5 or HD.5)
1 (it gives me N.25, BV.25, AV.5 or HD.5)

Please help?

[Tony Valko]

Try this...

Data Range

	D	E	F	G	H	I
6	N.25	BV.25	AV.25	HD.25		10
7	N.5	BV.5	AV.5	HD.5		3
8	N.75	BV.75	AV.75	HD.75		BV.75
9	N	BV	AV	HD

This formula entered in cell I8:

=LOOKUP(I6,{0,10,20,30},CHOOSE(I7,D6:G6,D7:G7,D8:G8,D9:G9))