Need help with search formula

How does Numbers know which is "the selected row"?

How does Numbers know what is the "specific label"? How is it specified?

What result is returned when the label is found? To where is that result returned?

From the looks of the table, the search is being done from the columns to the right, each column is searching for one of the labels that fit its category, and it is the found label that is to be the returned result. Looks like a record of who wins face-offs against whom, and under what circumstances.

For starters, I'd try to get stricter order into the recorded data, and revise the recording to allow fewer entries during the game itself.

Assuming the rows are recorded in the order the events occur:

Period will change a maximum of five times, and the changes will always occur in the same order. Reserve a column for that information only, and record the period only for the first event in that period.

Time has a maximum of three different values during each period. For OT, there is no "MID 10", and for SO, there is no specific time period. Again record the time for only the first event during that time category (in each period). Reserve a second column for this data.

Most cells in column F contain the current status of the game. This changes only when a team scores, and that change is followed immediately by a faceoff. Record the new status only on the line that faceoff take place. (Which column does this data go into when gathered at the right)?

Relative strength changes only when a penalty is take or a penalty expires. Again, code only when there's a change.

RHC, LHC: Opp Shot? From the pattern, it seems to refer to the Anaheim players, and is always the same for the same player. If true, does it need to be recorded at all? If the players do not 'switch hit', then RHC/LHC can be derived from the identity of the player.

Rows 3 and 4 contain the same player names/ Is there any significance to them not being in the same order in the two rows?

"Points" appears to refer to the 'bare' number that appears in one column in every row. This seems to be the fly in the ointment—the piece of data that gets recorded in the 'wrong' column and makes the very complex search necessary. What is it about this piece of data that causes it to be recorded in three different columns in the various rows? Solving that woudl simplify the issue greatly.

Regards,

Barry

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)

Drag-fill 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.

I noticed an error in the formula. In the post above, there is a missing closing parenthesis on the first IFERROR and an extra one after the last one.

Hi Hugh,

The questions in my first post were ones I asked myself (and you) to try for an understanding of the parameters, and purpose, of the exercise. By the time I posted, my thinking was headed in a similar direction to Badunit's, bt at 2:00 AM, it was time to let those thoughts simmer over night and through much of the next busy day.

In a similar case, my approach would be similar to Badunits, and I would use a table to hold the possible values for each column.

But like many, this is a 'special' case. Other than the player rosters for each team, all the columns have a small number of possible results, and an even smaller number of original data patterns to detect in order to return the correct result.

That being so, I opted to include the 'key characteristics" of those values in the formula itself for most of the columns.

On Functions:

The difficulty with using LOOKUP for this job is that LOOKUP accepts a 'close match,' and does not accept wild cards. 'Close match' in this instance is defined as "the largest value that is less than or equal to the target value." To ensure against the 'odd results' you noted, it is necessary to include every possible value for each variable in the search terms—often not an easy task.

HLOOKUP, VLOOKUP and MATCH, on the other hand, can be set to accept only an 'exact match.' And with 'exact match' come the opportunity to use wild cards.

Both are put to use in the main formula(s) below. With two exceptions, all of the formulas are similar, differing only in the numbers of comparisons made and the values each looks for. Each requires one iteration of the basic block: IFERROR(MATCH(search-value, search-where, find-value),next step) for each possible key value in that column.

Leaving out the two 'roster' positions, the first summary column, Period, contains the largest number of distinct two character keys. And, as there is a one-to-one match between these keys and the full term, we can skip using a shortened key in this column.

Here's the formula for column J, "Period", as appears in cell J2. As in Badunit's example, I've inserted returns to make the five individual blocks more clear:

J2: =OFFSET($A$1,ROW()-1,

IFERROR(MATCH("1st",$A2:$I2,0),

IFERROR(MATCH("2nd",$A2:$I2,0),

IFERROR(MATCH("3rd",$A2:$I2,0),

IFERROR(MATCH("OT",$A2:$I2,0),

IFERROR(MATCH("SO",$A2:$I2,0),

"NF")))))-1)

Aside: I'm wondering if the last MATCH section is necessary. Are there face-offs in the "SO"?

Column K, "Strength" gets the same formula, but with only three keys, each with two characters and the wild card (*):

K2: =OFFSET($A$1,ROW()-1,

IFERROR(MATCH("PP*",$A2:$I2,0),

IFERROR(MATCH("EV*",$A2:$I2,0),

IFERROR(MATCH("PK*",$A2:$I2,0),

"NF")))-1)

Column L (Location)also has nine possible vales, but only 5 necessary two character plus wild card keys:

L2: =OFFSET($A$1,ROW()-1,

IFERROR(MATCH("OZ*",$A2:$I2,0),

IFERROR(MATCH("DZ*",$A2:$I2,0),

IFERROR(MATCH("ON*",$A2:$I2,0),

IFERROR(MATCH("DN*",$A2:$I2,0),

IFERROR(MATCH("Ce*",$A2:$I2,0)

,"NF")))))-1)

Column M, "Time" is the same as column K, with the three keys below:

"FI*", "MI*", "LA*"

Columns N and O, "Player" and "Opponent" are special cases, discussed below.

Column P, "OPP Shot" gives the first impression that only a single letter (plus the wild card) is needed to distinguish between the two possible values. That's true only if those values are isolated from the rest of the table, including the value "LAST 5". With that value included in the table, it's necessary to use at least a two letter (plus wild card) value to identify the two possible values here.

"LH*", "RH*"

Column Q, "Points" is uinique within the table in that the data to be brought in is the only datum in each row that is a (pure) number. That allows for a much simpler formula:

Q2: =MAX($A2:$I2)

There's one more set of data that hasn't been transferred into the summary columns; the set showing the current score in terms of who is leading and by how may goals. Here the formula would be the three keys one used in columns K and M.

Keys: "Up*", "Ti*" and "Do*"

As mentioned above, columns N and O are special cases. They are the only columns for which the possible values will change from game to game (for the Opponents) and through the season (for both teams), and each cell in these columns will contain one of a possible 18 different values (from a set of 36).

Badunit's approach of testing each value in the row An:In requires nine iterations of the test for each value.

My version requires fewer iterations in all columns but these two, but here requires as many as there are players who may take a face-off for the team in that column, with a different key set (entered manually) for each column. Doable, perhaps, but not the best.

Further thoughts on these two columns later.

Meantime, here's the results using the formulas shown or described above.

Regards,

Barry