I'm trying to produce a simplified set of data in a secondary worksheet from a very large table by matching 2 values with 2 columns. These values repeat multiple times in each column, I'm looking to find when they BOTH match. For example:

1---|  Sup  |       |       |
2---|       |   D   |       |
3---|  Sup  |   D   | Value | <<< This is the row I want to extract "C" from

My current formulas for obtaining the COLUMN and ROWS. The table is effectively a LARGE roster with dates as the columns after a few 'descriptor' columns. The column which contains 'Sup' is the role (where Sup = Supervisor). I'm trying to use functions to dynamically identify the supervisor on day shift (D) for the day.

First Row for "D"

=MATCH("D", MASTER[B]), 0)

First Row for "Sup"

=MATCH("Sup", MASTER[A], 0)

I can't tell easily when they match up as there are 7-8 Supervisors in the roster with only 1 ever being on day shift.

Any clues? Cheers!

P.S. I cannot use Macros or VB for this as it's locked down by group policy!

  • 1
    It looks like you're trying to match both "D" and "Sup" in the same column. What column heading distinguishes the columns you have labeled "--A--" and "--B--"? – Bandersnatch Apr 6 '18 at 13:29
  • Apologies Bandersnatch, that's me being lazy and copy pasting things without appropriately editing them, I've updated them accordingly. – Jovin_ Apr 8 '18 at 8:10
  • When they both match what? You say they repeat in each column. Are you comparing to an earlier entry in the same column or simply the presence of a value in both A and B? Just the first case where this happens or every case? – fixer1234 Apr 8 '18 at 8:20
  • Hey fixer1234, I've added some additional context in the edit for clarification. – Jovin_ Apr 9 '18 at 21:45
  • I'm still not 100% sure I understand the question, but if you're looking for the row that contains Sup in col A and D in col B, a simple solution is to add a helper column that contains the concatenation of A&B. Then MATCH SupD (target) in the helper column. – fixer1234 Apr 11 '18 at 3:26

This formula should do it for you:

=if(A1="D",if(B1="Sup",C1,"Not Matched"),"Not Matched")

You could put the above in D1 and copy it down. Column D will then contain the value of column C when A=D and B=Sup.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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