if value is between two numbers

[samuel]

I have the following ranges:

A B C D
GENERAL 110 0.00 $0.21
GENERAL 125 0.22 $10.00
GENERAL 130 10.01 $50.00
GENERAL 140 50.01 $100.00
GENERAL 150 100.01 $500.00
GENERAL 160 500.01
GENERAL2 10 0.00 $0.21
GENERAL2 20 0.22 $10.00
GENERAL2 30 10.01 $50.00
GENERAL2 40 50.01 $100.00
GENERAL2 50 100.01 $500.00
GENERAL2 60 500.01

I want to search
3.98 GENERAL

where the function would lookup 'GENERAL' in the stated ranges find which
row the value of '3.98' is and return the value in column B.

any ideas?

 

[Mike H]

Samuel,

I don't see a value of 3.98

Mike

 

[samuel]

correct. 3.98 would be between 0.22 & 10.00

so the value needing to be returned is 125

 

[RagDyer]

Try this *array* formula,
Where your variables are assigned to particular cells.
General in E1
3.98 in E2

=INDEX(B1:B12,MATCH(1,(E2>=C1:C12)*(E2<=D112)*(A1:A12=E1),0))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

 

[Teethless mama]

Try this:

=INDEX(B1:B12,MAX(INDEX((A1:A12="General")*(C1:C12<=3.98)*ROW(C1:C12),)))

 

[RagDyer]

Yep, I missed that >500!<g>

 

[RagDyer]

Use TM's formula!