IF And statement for multiple cells

[the87boy]

I have an Excel Document, which looks like this

A | B | C
1 | 1.9 | a
2 | 2.9 | b
...

In E I have some numbers e.g. 2.5 and 1.1, and in F I want the result from
=IF(NOT(AND(E2>=A1;E2<=B1));IF(NOT(AND(E2>=A2;E2<=B2));"Error";C2);B2)

I have about 20 numbers, so the IF statements will be very huge, so I hope somebody in here, have a better way

 

[Ron Coderre]

Using your posted example, I think this regular formula would work for you
F1: =VLOOKUP(E1,$A$1:$C$2,3,1)

and...if you have Excel 2007 or later this formula will trap any errors:
F1: =IFERROR(VLOOKUP(E1,$A$1:$C$2,3,1),"error")

Copy that formula down as far as you need.

Is that something you can work with?

 

[the87boy]

It didn't work for me
It returns A3 or B3

 

[the87boy]

I saw an error in my formula
The right formula is
=IF(NOT(AND(E2>=A1;E2<=B1));IF(NOT(AND(E2>=A2;E2<=B2));"Error";C2);C1)

 

[the87boy]

The formula will be even better, if it returns 2 or 1 like this
=IF(NOT(AND(E2>=A1;E2<=B1));IF(NOT(AND(E2>=A2;E2<=B2));"Error";2);1)

 

[Ron Coderre]

It didn't work for me
It returns A3 or B3

Not possible...those cells are not referenced in the formula.

With
A1: 1
A2: 2

B1: 1.9
B2: 2.9

C1: a
C2: b

and...
E1: 2.5

The formula I posted: =VLOOKUP(E1,$A$1:$C$2,3,1)
would return: b
since there are no other values beyond Row_2 to find.

If E1: 1.1
the formula would return: a (because 1.1 is >=1 and <2)

If you enter your entire table of 20 values...in ascending order,
that formula should return the correct corresponding Col_C value.

Does that help?...or do you need something else?

 

[the87boy]

Look at my thread at http://www.mrexcel.com/forum/showthread.php?p=2830228#post2830228 as I saw an error in the formula