Compare the value to another workbook with 2 rules that need to be pass both

user_19150697 0
Viewed 62

I have been trying to check the condition using VBA, but now I am struggling here is the explanation of what I am doing

  1. if there's any 'uncheck' on condition(A) column

  2. then, The objective is comparison between data2 and data4 (which is the data of that itself uncheck condition) with data2 and data4 in another workbook (photo2) (not another worksheet)

  3. To compare it to another workbook (photo2), I need to compare whether the value in data2(C) column in (photo1) exists in data 2 in another workbook (photo2) if not we conclude that not pass as a final result in F column

    for example, as you can see A5 & A7 in photo2 pass 1st rule because they have the same value as C5 & C7

  4. here is the difficult part, when it pass 3.) rule, then I need to compare data4(E) column in (photo1) to data4 in another workbook (photo2) But the rule is the value does not need to be exactly the same. however if the data4 value in another workbook (photo2) isn't more/less than 50 compare to the value in data4 in photo1 then we conclude that pass as a final result

    for example, as you can see from 3.) A5 & A7 pass rule but in 4.) only A5 with 220 pass because the difference is 20 compare to 200 which in range 150-250 (200+-50)

photo1

photo 1

photo2

photo2

Is there any formula/vlookup/vba or whatever to run it?

vlookup match excel-formula vba excel
1 Answers

if you have the following data in your worksheets

Worksheet Photo1 enter image description here

Worksheet Photo2 enter image description here

you can use this formula in result in column F

=IF(A2="uncheck",IF(NOT(ISERROR(MATCH(C2,Photo2!A:A,0))),IF(ABS(INDEX(Photo2!B:B,MATCH(C2,Photo2!A:A,0))-E2)<=50,"pass","not pass"),"not pass"),"not pass")

Explanation what the formula does

=IF(A2="uncheck",
    IF(NOT(ISERROR(MATCH(C2,Photo2!A:A,0))),
        IF(ABS(INDEX(Photo2!B:B,MATCH(C2,Photo2!A:A,0))-E2)<=50,"pass","not pass"),
    "not pass"),
"not pass")

This part IF(NOT(ISERROR(MATCH(C2,Photo2!A:A,0))) checks if data2 exists in Photo2 if not it does not match and returns an error (not pass) if there is no error NOT(ISERROR it will check the following:

IF(ABS(INDEX(Photo2!B:B,MATCH(C2,Photo2!A:A,0))-Photo1!E2)

Where INDEX(Photo2!B:B,MATCH(C2,Photo2!A:A,0)) will pull the data4 value from Photo2 by matching the data2 from Photo1 with the data2 from the Photo2 with MATCH(C2,Photo2!A:A,0). Then subtract data4 from Photo1 get the absolute value ABS of it and test if this is <=50. Then it is pass otherwise not.

user_32196130
Related