Comparing multi cell values against same

Closed
Report
-
 Trowa -
I have a column which contains 1 of 3 old_values; 267, 268, 271
I am expecting them to be changed respectively in new_column to; 238, 240, 223

I want a formula that states:
if C#=267 and F#238, then pass, else fail
if C#=268 and F#240, then pass, else fail
if C#=271 and F#223, then pass, else fail

But I want that for ALL 3 values;

i.e., =if(C#=267,268,271 and F#238,240,223,"pass","fail")

old new Result
267 238 Pass
268 240 Pass
271 223 Pass
267 222 Fail
268 212 Fail
271 214 Fail

1 reply

Hi J,

Here's my solution to your query:

Column A (old)
267
268
271
267
268
271

Column B (new)
238
240
223
222
212
214

Column C (Result)
=IF(D1="Pass","Pass",IF(E1="Pass","Pass",IF(F1="Pass","Pass","Fail")))
=IF(D2="Pass","Pass",IF(E2="Pass","Pass",IF(F2="Pass","Pass","Fail")))
=IF(D3="Pass","Pass",IF(E3="Pass","Pass",IF(F3="Pass","Pass","Fail")))
=IF(D4="Pass","Pass",IF(E4="Pass","Pass",IF(F4="Pass","Pass","Fail")))
=IF(D5="Pass","Pass",IF(E5="Pass","Pass",IF(F5="Pass","Pass","Fail")))
=IF(D6="Pass","Pass",IF(E6="Pass","Pass",IF(F6="Pass","Pass","Fail")))

Column D , You can hide this column or make the textcolor white.
=IF(A1=267,IF(B1=238,"Pass","Fail"),"Fail")
=IF(A2=267,IF(B2=238,"Pass","Fail"),"Fail")
=IF(A3=267,IF(B3=238,"Pass","Fail"),"Fail")
=IF(A4=267,IF(B4=238,"Pass","Fail"),"Fail")
=IF(A5=267,IF(B5=238,"Pass","Fail"),"Fail")
=IF(A6=267,IF(B6=238,"Pass","Fail"),"Fail")

Column E , You can hide this column or make the textcolor white.
=IF(A1=268,IF(B1=240,"Pass","Fail"),"Fail")
=IF(A2=268,IF(B2=240,"Pass","Fail"),"Fail")
=IF(A3=268,IF(B3=240,"Pass","Fail"),"Fail")
=IF(A4=268,IF(B4=240,"Pass","Fail"),"Fail")
=IF(A5=268,IF(B5=240,"Pass","Fail"),"Fail")
=IF(A6=268,IF(B6=240,"Pass","Fail"),"Fail")

Column F , You can hide this column or make the textcolor white.
=IF(A1=271,IF(B1=223,"Pass","Fail"),"Fail")
=IF(A2=271,IF(B2=223,"Pass","Fail"),"Fail")
=IF(A3=271,IF(B3=223,"Pass","Fail"),"Fail")
=IF(A4=271,IF(B4=223,"Pass","Fail"),"Fail")
=IF(A5=271,IF(B5=223,"Pass","Fail"),"Fail")
=IF(A6=271,IF(B6=223,"Pass","Fail"),"Fail")

Hoping my solution will satisfy your needs.
Best regards,
Trowa