# excel formula, I want to know EXCEL FORMULA for multiple conditions (7 conditions) I have excel sheet with numeric data 15 rows and 4 columns(A,B,C,D). (Excel Image attached, in image E column and F column added for explanation only) Required formula for following conditions.(image)

Actually formula need to apply for covering only two row if suppose required output DATA for row#3 (A3) than ONLY above row#2 (B,C,D) data needs to consider in formula, means for A3 data need to consider row#2 B2,C2,D2) for A4 data need to consider B3,C3,D3) likewise same for other rows.

A3 NOT HIGHER THAN B2 AND NOT LOWER THAN C2 "NORMAL"

A5 HIGHER THAN B4 "HIGHER"

A6 LOWER THAN B5 "LOWER"

A8 FIRST 3 DIGIT MATCH WITH C7 AND D7 "L-SIRIES"

A11 FIRST 3 DIGIT MATCH WITH B10 AND D10 "H-SERIES"

A13 FIRST 3 DIGIT MATCH WITH B12 "H-RANGE"

A14 FIRST 3 DIGIT MATCH WITH B13 "L-RANGE"

currently I tried below formulas.

=IF(A3>B2,"HIGHER",IF(A3C3,"NORMAL",""))) (somehow after writing above formula here in BOX <> sign not displaying once I save my edits)

I tried another for "H-RANGE" =IF(LEFT(A3,3)=LEFT(B2,3),IF(A3=B2,TRUE, FALSE),TRUE) [getting Output but result is not accurate.

I tried another for "L-RANGE" =IF(LEFT(A3,3)=LEFT(C2,3),IF(A3=C2,TRUE, FALSE),TRUE) getting result but result is not accurate.

Conditional Formatting FORMULA also fine for highlight "A" column WITH DIFFERENNT condition match color.

• Please add the formulas that you have tried – Gangula Jul 23 '19 at 11:37
• currently I am using =IF(A3>B2,"HIGHER",IF(A3<C2,"LOWER",IF(B3<>C3,"NORMAL",""))) and getting result for "HIGHER", "LOWER" and "NORMAL" but output NORMAL is not accurate and I need help for "H-SERIES" , "H-RANGE" , "L-SERIES" , "L-RANGE" – JUSTWIN Jul 23 '19 at 16:30
• I tried another for "H-RANGE" =IF(LEFT(A3,3)=LEFT(B2,3),IF(A3=B2,TRUE, FALSE),TRUE) [getting Output but result is not accurate. – JUSTWIN Jul 23 '19 at 16:38
• I tried another for "L-RANGE" =IF(LEFT(A3,3)=LEFT(C2,3),IF(A3=C2,TRUE, FALSE),TRUE) getting result but result is not accurate. – JUSTWIN Jul 23 '19 at 16:39
• You explain a lot of the conditions with terms that aren't meaningful to anyone but you. In the 1st three, you can compare two cells, but you've added qualifiers normal, higher, and lower. What do we do with those terms? Similarly, the last four refer to series and ranges. Those terms appear in col E as "explanation", but it isn't obvious what's being explained or how to use the information. There's an indication that those are actually desired outputs, but then the conditions that produce those outputs aren't clear. (cont'd) – fixer1234 Jul 23 '19 at 23:57 How it works:

• Enter this Formula in Cell `AF2` & fill down.

`=IF(AND(LEFT(AA2,3)=LEFT(AC1,3),LEFT(AA2,3)=LEFT(AD1,3)),"Lseries",IF(AND(LEFT(AA2,3)=LEFT(AB1,3),LEFT(AA2,3)=LEFT(AD1,3)),"Hseries",IF(AND(LEFT(AA2,3)=LEFT(AB1,3)),"HRange",IF(AA2>AB1,"Higher",IF(AA2<AC1,"Lower",IF(AB2<>AC1,"Normal","LRange"))))))`

N.B.

• Formula does't returns `LRange` for `Row 14`, because first 3 digits of the value in `AA14` doesn't matches with first 3 digits of `AB13`.

• Check values in both cells in BLUE color.

You may adjust cell references in the formula as needed.

• RAJESH SIR, Thank YOU SO MUCH for your PRECIOUS efforts. – JUSTWIN Jul 24 '19 at 6:17
• RAJESH SIR, Thank YOU SO MUCH for your PRECIOUS efforts. There is little mismatching forrow14 LRange - but LET me try from my end with another DATA, will update you if need your help to modify in your provided formula.. Thank You Genius. – JUSTWIN Jul 24 '19 at 6:23
• @JUSTWIN,, read the N.B. with my post I've described Y `Row 14` doesn't matches,, since it's check with uneven pattern so it may vary in cell/cells, depends on set or Rows !! – Rajesh S Jul 24 '19 at 6:27
• @ Rajesh S. Thank You SIR, Yes I noted your comment about Row 14. I am happy to inform that with your PERFECT Formula, I just made little changes now RESULT is SUPERB.. Thank you for your HELP and SUPPORT. – JUSTWIN Jul 24 '19 at 8:11
• =IF(AND(LEFT(N4,3)=LEFT(P3,3),LEFT(N4,3)=LEFT(Q3,3)),"Lseries",IF(AND(LEFT(N4,3)=LEFT(O3,3),LEFT(N4,3)=LEFT(Q3,3)),"Hseries",IF(N4>O3,"Higher",IF(N4<P3,"Lower",IF(AND(LEFT(N4,3)=LEFT(O3,3)),"HRange",IF(AND(LEFT(N4,3)=LEFT(P3,3)),"LRange",IF(N4=O4,"Open=High","Normal"))))))) – JUSTWIN Jul 24 '19 at 8:13