Ranges

[sureshsonti]

I am developing a form which will look for a number from a range based on the result total. I created 2 named ranges one is training and the second is writing.

Not sure how else I can design the range sheet.

Any suggestions will help me a lot.

Thanks

 

[Hui]

L3: =VLOOKUP(training,G4:I8,3)
L4: =VLOOKUP(writing,G9:I13,3)

 

[sureshsonti]

I am developing a form which will look for a number from a range based on the result total. I created 2 named ranges one is training and the second is writing.

Not sure how else I can design the range sheet.

Any suggestions will help me a lot.

Thanks

Thanks Mr. Hui. I was over thinking about this issue. Your solution was simple and I know vlookups. Not sure why I didnt think out Vlookups.

Thanks again for your help

 

[bosco_yip]

Or…………………

In L3, formula copied down to L4 :

=VLOOKUP(K3&INDIRECT(K3),INDEX(TEXT($F$4:$H$13,";;")&$G$4:$I$13,0),3)

Regards
Bosco

 

[sureshsonti]

Or…………………

In L3, formula copied down to L4 :

=VLOOKUP(K3&INDIRECT(K3),INDEX(TEXT($F$4:$H$13,";;")&$G$4:$I$13,0),3)

Regards
Bosco

Thanks Mr. Bosco.

Or…………………

In L3, formula copied down to L4 :

=VLOOKUP(K3&INDIRECT(K3),INDEX(TEXT($F$4:$H$13,";;")&$G$4:$I$13,0),3)

Regards
Bosco

Mr. Bosco,
If the result is 7 which is between 1 - 10 the formula is picking the last range result 49. Can you check this. Not sure why. Please see attached

Thanks

 

[bosco_yip]

Mr. Bosco,
If the result is 7 which is between 1 - 10 the formula is picking the last range result 49. Can you check this. Not sure why. Please see attached

Changed the L3 formula to

=VLOOKUP(INDIRECT(K3),IF(K3="Training",G$4:I$8,G$9:I$13),3)

and copied down to L4

Regards
Bosco

 

[sureshsonti]

Changed the L3 formula to

=VLOOKUP(INDIRECT(K3),IF(K3="Training",G$4:I$8,G$9:I$13),3)

and copied down to L4

Regards
Bosco

Thanks Mr. Bosco. This works now. Thanks for your help