I need help getting a formula to give me the information I need, so far nothing I've tried has worked so far. I need to search the desired row for a specific label or instance, which would normally be simple but here's where I am stuck. The result can be 1 out of up to 20 different labels.
A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q 
descriptors... 







 Period  Strength  Location  Time  Player  Opponent  OPP Shot  Points 
LHC  EV 55  1st  Center  FIRST 5  Tied  33 H SEDIN  11 KOIVU  2 








LHC  PP 54  1st  OZ L  FIRST 5  Tied  11 KOIVU  33 H SEDIN  2 








LHC  PP 54  1st  OZ R  FIRST 5  Tied  11 KOIVU  25 EBBETT  3 








RHC  PP 54  1st  OZ L  FIRST 5  Tied  33 H SEDIN  15 GETZLAF  2 








LHC  1  EV 55  1st  DNZ L  FIRST 5  Tied  25 EBBETT  11 KOIVU 








RHC  EV 55  1st  OZ R  MID 10  Tied  15 GETZLAF  36 HANSEN  1 








RHC  EV 55  1st  DZ L  MID 10  Tied  08 SELANNE  40 LAPIERRE  1 








LHC  EV 55  1st  ONZ R  MID 10  Tied  33 H SEDIN  11 KOIVU  2 








RHC  EV 55  1st  Center  MID 10  Down 1  40 LAPIERRE  15 GETZLAF  2 








LHC  EV 55  1st  OZ L  MID 10  Down 1  33 H SEDIN  13 BONINO  1 








LHC  EV 55  1st  OZ L  MID 10  Down 1  25 EBBETT  11 KOIVU  0 








LHC  EV 55  1st  Center  MID 10  Tied  27 MALHOTRA  13 BONINO  3 








LHC  PP 54  1st  OZ R  MID 10  Tied  33 H SEDIN  11 KOIVU  0 








RHC  EV 55  1st  ONZ R  MID 10  Up 1  67 RAKELL  33 H SEDIN  1 








RHC  EV 55  1st  OZ L  MID 10  Up 1  40 LAPIERRE  67 RAKELL  3 








LHC  EV 55  1st  ONZ L  LAST 5  Up 1  27 MALHOTRA  3  11 KOIVU 








RHC  EV 55  1st  OZ L  LAST 5  Up 1  15 GETZLAF  40 LAPIERRE  0 








RHC  PK 45  1st  DZ R  LAST 5  Up 1  08 SELANNE  1  27 MALHOTRA 








RHC  PK 45  1st  LAST 5  DZ R  Up 1  08 SELANNE  27 MALHOTRA  1 








RHC  PK 45  1st  LAST 5  DZ R  Up 1  27 MALHOTRA  08 SELANNE  1 








LHC  EV 55  Center  1st  LAST 5  Tied  11 KOIVU  1  27 MALHOTRA 








LHC  EV 55  1st  DNZ R  LAST 5  Tied  25 EBBETT  1  11 KOIVU 








LHC  EV 55  1st  LAST 5  OZ R  Tied  33 H SEDIN  11 KOIVU  1 








RHC  EV 55  1st  LAST 5  OZ R  Tied  27 MALHOTRA  67 RAKELL  2 








So in columns AI are the many different labels that we code during the game, in the order we code them. Ideally it would be easiest if we followed a pattern, however during live gameplay we code when it happens. In columns JQ are where we want our results, and from there we will copy & paste into our game summarry docs. So for example take Period, simple the result can: 1st, 2nd, 3rd, OT or SO. But we also have things like Location (9 results) and Player & Opponent (up to 18). I need the formula to search the selected row for a specific label, and there can only be 1 possible result (based on what we code).
Any help at all would be extremely appreciated!
You can use MATCH and OFFSET with a lookup table. It is a long formula but it will work. I started on it as shown in the screenshot below.
You need a lookup table that includes all of the categories of data. The screenshot shows the start of that table. I wasn't sure what to put in the "strength" category so I left it blank. The order of categories needs to be the same order as in your first table, columns J through whatever.
The formula in J (with a few returns thrown in so you can more easily see the pattern) is
=OFFSET(Table 2 :: A$1,
IFERROR(MATCH($A,Table 2 :: A,0),0+
IFERROR(MATCH($B,Table 2 :: A,0),0)+
IFERROR(MATCH($C,Table 2 :: A,0),0)+
IFERROR(MATCH($D,Table 2 :: A,0),0)+
IFERROR(MATCH($E,Table 2 :: A,0),0)+
IFERROR(MATCH($F,Table 2 :: A,0),0)+
IFERROR(MATCH($G,Table 2 :: A,0),0)+
IFERROR(MATCH($H,Table 2 :: A,0),0)+
IFERROR(MATCH($I,Table 2 :: A,0),0))1,0)
Dragfill to the rest of the columns and down to the bottom.
What the formula does is try to match the contents of columns A through I to the contents of the first column in your lookup table. Only one of those columns should have a match. The result is a number that points to the matching item in the lookup table. OFFSET uses that number to give you the item.