1

I have some difficulties with my IF formula. I have the following columns that represent some subjects and the points that I managed to get for those subjects (F:H). In column I , I would like to compare my points with another table(M:N) that contains the minimum amount of points needed to pass the course .If my score is equal or greater then what the corresponding score is in column N, I would like to show PASS or FAIL in column I. I Used the following formula but when comparing with M:N it keeps going down (outside the table) giving me in the end all the exams as passed.

 =IF(AND(F2:$F$6=M2:$M$6)&H2:$H$6>=N2:$N6;"PASS";"FAIL")

F                 H            I                      M                     N
(Subjects)       (Points)                          (Subjects)              (Points to Pass)
Mathematics       85                            Mathematics                 85
English           88                             English                    70
French            78                             French                     60
Chinese           56                             Chinese                    60
Physics           70                             Sports                     80
Sports            95                             Physics                    80
ORCos
  • 89
  • 1
  • 10
  • Use `INDEX` and `MATCH`. [Here](https://stackoverflow.com/a/58436658/9758194) is some explaination on how to use that combination of powerful functions. Or else a simple `SUMIF` to reference your value against. – JvdV Oct 23 '19 at 10:33

3 Answers3

1

Example formula should look like this:

=IF(H1>=INDEX($N$1:$N$6,MATCH(F1,$M$1:$M$6,0)),"PASS","FAIL")
zipa
  • 27,316
  • 6
  • 40
  • 58
1

Let your table layout housed in F1:N7 include header

In I2, formula copied down :

=IF(H2>=VLOOKUP(F2,M:N,2,0),"PASS","FAIL")
bosco_yip
  • 3,762
  • 2
  • 5
  • 10
1

You could use:

=IF(H2>=VLOOKUP(F2,$M$2:$N$7,2,FALSE),"PASS","FAIL")

Results

enter image description here

Error 1004
  • 7,877
  • 3
  • 23
  • 46