how to lookup a value in this case?

[makubexho]

hi, im puzzled w/ this simple question, please advise

a sheet with 2 columns, column A (sales volume), column B (grade), positioned as they are.

$5000-----C
$10000----B
$20000----A

A fruit vendor has a sale $4800

in a given cell, i want it to show me that this vendor deserves a grade C as his sale volume falls into the corrosponding class.

(i tried my way w/ "index+match", but it doesnt work as 4800 is not counted under $5000. however "vlookup" seems ok in this case if only these 2 columns are allowed to swap positions.)

 

[Andrew Poulsom]

You need:

$0-----C
$5001----B
$10001----A

 

[makubexho]

actually,all the data are fixed, in other words, adding 1 more row (i.e. 0----C) is not allowed, what'd you say then?

 

[Aladin Akyurek]

hi, im puzzled w/ this simple question, please advise

a sheet with 2 columns, column A (sales volume), column B (grade), positioned as they are.

$5000-----C
$10000----B
$20000----A

A fruit vendor has a sale $4800

in a given cell, i want it to show me that this vendor deserves a grade C as his sale volume falls into the corrosponding class.

(i tried my way w/ "index+match", but it doesnt work as 4800 is not counted under $5000. however "vlookup" seems ok in this case if only these 2 columns are allowed to swap positions.)

Try...

=LOOKUP(MAX(E2,MIN($A$2:$A$4)),$A$2:$A$4,$B$2:$B$4)

where E2 is a sale of interest like $4800 and A2:B4 houses the lookup table.

 

[makubexho]

this is great, what a good bypass. thanks ：D