Multiple requirements in formula for cell - how?

[Jools1961]

Cell R5 = IF A1 is less than B2 and more than B3 then R5 should equal F1, or if A1 is less than C2 and more than C3, then R5 should equal F2, or if A1 is less than D2 and more than D3, then R5 should equal F3.

Seems simple enough but I dont know how to do it. Can anyone help?Thanks heaps.

 

[acw]

Hi

=IF(AND(A1<B2,A1>B3),F1,IF(AND(A1<C2,A1>C3),F2,IF(AND(A1<D2,A1>D3),F3,"No match")))

It will put in No Match if things don't work.


Tony

 

[erik.van.geit]

Hi, acw,

I posted this about 10 minutes ago
=IF(AND(A1<B2,A1>B3),F1,IF(AND(A1<C2,A1>C3),F2,IF(AND(A1<D2,A1>D3),F3,"")))
blank if criteria not matched

then deleted it because I saw your post

but your solution doesn't work for me: is this a "short-syntax"
AND(A1B3) ?

kind regards,
Erik

 

[acw]

Erik

Don't know what happened. The formula should be

=IF(AND(A1<B2,A1>B3),F1,IF(AND(A1<C2,A1>C3),F2,IF(AND(A1<D2,A1>D3),F3,"No match")))


Don't know why the <> symbols didn't appear.


Tony

 

[acw]

Hmm

Chunks of the formula are being deleted.

=IF(AND(A1<B2,A1>B3),F1,IF(AND(A1<C2,A1>C3),F2,IF(AND(A1<D2,A1>D3),F3,"No match")))


Tony

 

[acw]

Try again

=IF(AND(A1<B2,A1>B3),F1,IF(AND(A1<C2,A1>C3),F2,IF(AND(A1<D2,A1>D3),F3,"No match")))

 

[erik.van.geit]

Sorry, Julie,

My reply was also send with<> signs
Is this sheetsample clear to you?
kind regards,
Erik

Map1
	A	B	C	D	E	F
1	13					F1
2		12	14	16		F2
3	F2	10	11	12		F3
Blad1

 

[erik.van.geit]

acw,

take a look here for a solution to this problem
http://www.mrexcel.com/board2/viewtopic.php?p=641227#641227