Hi..
I am trying to solve the if(and.. formula from the attached file in column "H")

This is what I am trying to do:

if"g8" is = to "C21" then give me "D21" And if "g8" is >"B22" but < than "C22" then give me "D22"... and so on till line 29.

I think that the formula If(And.. is probably a good one but I do not know how to make it.

thanks

Attached Files

Book1.xlsx (13.8 KB, 12 views)

Perhaps =LOOKUP($G8,$C$21:$C$29,$D$21:$D$29)

Drag down as needed

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thank,

But I would be more interested in understanding how if(and... works to keep the range that I have between the numbers.

Could you be a little more explicit please?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Paxon - it appears you might be unfamiliar with the LOOKUP function (as opposed to VLOOKUP and HLOOKUP). The formula as written by Pecoflyer using LOOKUP negates the need for complicated, nested IF formulas.

Here is some good info on the LOOKUP formula:
https://support.office.com/en-us/art...1-369d5e3864cb
One statement from that page that applies to your specific instance is:
If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.

Hope this helps!

Sure,

I will attached the excel file. Column "I" is exactly the formula I was looking for. But if you look "I12" the formula should give me the value of 3 because the range 61-80 (as per D25) and not FALSE.

I do not understand why is not working.

Attached Files

Book1.xlsx (14.2 KB, 12 views)

Oops - the formula as written by Pecoflyer should be:
=LOOKUP($G8,$B$21:$B$29,$D$21:$D$29)
(looking at Col B, rather than Col C)
If you paste that formula in I8, and drag down, you will get the results you expect.

Pecoflyer gave you by far the best formula (one function call), but if you absolutely want to use a complicated IF(AND-construction with a lot more function calls then:

Attached Files

paxon_2.xlsx (14.7 KB, 8 views)

thanks to all of you for your help!!!