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:
++++|---A---|---B---|---C---| 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!