Skip navigation

Need help with search formula

457 Views 6 Replies Latest reply: Mar 21, 2013 12:48 AM by Barry RSS
Hugh Dunlop Calculating status...
Currently Being Moderated
Mar 19, 2013 11:20 PM

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 5-5

1st

Center

FIRST 5

Tied

33 H SEDIN

11 KOIVU

2

 

 

 

 

 

 

 

 

LHC

PP 5-4

1st

OZ L

FIRST 5

Tied

11 KOIVU

33 H SEDIN

2

 

 

 

 

 

 

 

 

LHC

PP 5-4

1st

OZ R

FIRST 5

Tied

11 KOIVU

25 EBBETT

3

 

 

 

 

 

 

 

 

RHC

PP 5-4

1st

OZ L

FIRST 5

Tied

33 H SEDIN

15 GETZLAF

2

 

 

 

 

 

 

 

 

LHC

1

EV 5-5

1st

DNZ L

FIRST 5

Tied

25 EBBETT

11 KOIVU

 

 

 

 

 

 

 

 

RHC

EV 5-5

1st

OZ R

MID 10

Tied

15 GETZLAF

36 HANSEN

1

 

 

 

 

 

 

 

 

RHC

EV 5-5

1st

DZ L

MID 10

Tied

08 SELANNE

40 LAPIERRE

1

 

 

 

 

 

 

 

 

LHC

EV 5-5

1st

ONZ R

MID 10

Tied

33 H SEDIN

11 KOIVU

2

 

 

 

 

 

 

 

 

RHC

EV 5-5

1st

Center

MID 10

Down 1

40 LAPIERRE

15 GETZLAF

2

 

 

 

 

 

 

 

 

LHC

EV 5-5

1st

OZ L

MID 10

Down 1

33 H SEDIN

13 BONINO

1

 

 

 

 

 

 

 

 

LHC

EV 5-5

1st

OZ L

MID 10

Down 1

25 EBBETT

11 KOIVU

0

 

 

 

 

 

 

 

 

LHC

EV 5-5

1st

Center

MID 10

Tied

27 MALHOTRA

13 BONINO

3

 

 

 

 

 

 

 

 

LHC

PP 5-4

1st

OZ R

MID 10

Tied

33 H SEDIN

11 KOIVU

0

 

 

 

 

 

 

 

 

RHC

EV 5-5

1st

ONZ R

MID 10

Up 1

67 RAKELL

33 H SEDIN

1

 

 

 

 

 

 

 

 

RHC

EV 5-5

1st

OZ L

MID 10

Up 1

40 LAPIERRE

67 RAKELL

3

 

 

 

 

 

 

 

 

LHC

EV 5-5

1st

ONZ L

LAST 5

Up 1

27 MALHOTRA

3

11 KOIVU

 

 

 

 

 

 

 

 

RHC

EV 5-5

1st

OZ L

LAST 5

Up 1

15 GETZLAF

40 LAPIERRE

0

 

 

 

 

 

 

 

 

RHC

PK 4-5

1st

DZ R

LAST 5

Up 1

08 SELANNE

1

27 MALHOTRA

 

 

 

 

 

 

 

 

RHC

PK 4-5

1st

LAST 5

DZ R

Up 1

08 SELANNE

27 MALHOTRA

1

 

 

 

 

 

 

 

 

RHC

PK 4-5

1st

LAST 5

DZ R

Up 1

27 MALHOTRA

08 SELANNE

1

 

 

 

 

 

 

 

 

LHC

EV 5-5

Center

1st

LAST 5

Tied

11 KOIVU

1

27 MALHOTRA

 

 

 

 

 

 

 

 

LHC

EV 5-5

1st

DNZ R

LAST 5

Tied

25 EBBETT

1

11 KOIVU

 

 

 

 

 

 

 

 

LHC

EV 5-5

1st

LAST 5

OZ R

Tied

33 H SEDIN

11 KOIVU

1

 

 

 

 

 

 

 

 

RHC

EV 5-5

1st

LAST 5

OZ R

Tied

27 MALHOTRA

67 RAKELL

2

 

 

 

 

 

 

 

 

 

So in columns A-I 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 J-Q 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!

numbers, OS X Mountain Lion (10.8.2)
  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Mar 20, 2013 1:58 AM (in response to Hugh Dunlop)

    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

  • Badunit Level 6 Level 6 (10,765 points)
    Currently Being Moderated
    Mar 20, 2013 12:33 PM (in response to Hugh Dunlop)

    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.

     

    Screen Shot 2013-03-20 at 3.17.17 PM.png

     

    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.

  • Badunit Level 6 Level 6 (10,765 points)
    Currently Being Moderated
    Mar 20, 2013 4:53 PM (in response to Hugh Dunlop)

    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.

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Mar 21, 2013 12:48 AM (in response to Hugh Dunlop)

    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.

    Picture 3.png

     

    Regards,

    Barry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.