How to compare multiple values from different columns in excel?

Question

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

Use INDEX and MATCH. Here is some explaination on how to use that combination of powerful functions. Or else a simple SUMIF to reference your value against. — JvdV
– JvdV, Commented Oct 23, 2019 at 10:33

Error 1004 · Accepted Answer · 2019-10-23 10:48:54Z

1

You could use:

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

Results

answered Oct 23, 2019 at 10:48

Error 1004

8,2863 gold badges36 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zipa · Accepted Answer · 2019-10-23 10:40:37Z

1

Example formula should look like this:

=IF(H1>=INDEX($N$1:$N$6,MATCH(F1,$M$1:$M$6,0)),"PASS","FAIL")

answered Oct 23, 2019 at 10:40

zipa

28k6 gold badges45 silver badges62 bronze badges

1 Comment

zipa Over a year ago

@JvdV I'm just promoting the best practice :) Thanks!

bosco_yip · Accepted Answer · 2019-10-23 10:41:05Z

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")

answered Oct 23, 2019 at 10:41

bosco_yip

3,8322 gold badges8 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to compare multiple values from different columns in excel?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related