1

I have some columns in sheet 1 (here in image O and P columns), and in another sheet (here in image R, S, T, U, V, W). I want to check the matching Date and Number from sheet 1 with Date and Number with sheet 2, and then do conditional formatting to highlight that particular row in sheet 2.

enter image description here

1 Answer 1

1

You can set a custom formula to R2:W in conditional formatting that reads:

=if($R2="",false,match(join("-",$S2,$R2),byrow($O$2:$O,lambda(each,join("-",each,offset(each,0,1)))),0))

It joins both columns in the correct orders and finds a match in the corresponding column also joined of the left

enter image description here


Option 2:

=IF($R2="",false,QUERY($O:$P,"Select O where O = "&$S2&" AND P = date '"&text($R2,"YYYY-MM-DD")&"'"))
8
  • Thanks. That's a great help, but it seems it will take a lots of time to load the formula for huge data. Commented Nov 11, 2022 at 14:09
  • You can set a new column (say Q) with that Byrow function, and just replace the formula with: =if($R2="",false,match(join("-",$S2,$R2),Q:Q,0)) That would be much faster, since the calculations are done just once
    – Martín
    Commented Nov 11, 2022 at 14:12
  • You could also just try with a QUERY: =IF($R2="",false,QUERY($O:$P,"Select O where O = "&$S2&" AND P = date '"&text($R2,"YYYY-MM-DD")&"'")) - VLOOKUP won't work with more than one variable, this would be its alternative
    – Martín
    Commented Nov 11, 2022 at 14:20
  • Thanks, but still cant handle a huge data. Is there a way I can add a new column with values as "PAID" if found the match and blank if nothing found? Commented Nov 11, 2022 at 14:46
  • Mmm, yes, obviously you can, but the approach would be similar, son't know if it would mean much difference in time. Have you tried adding that Q column?? That would diminish considerably the time of processing
    – Martín
    Commented Nov 11, 2022 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.