How to extract a handicap value from column based on 2 criteria-CodePudding

Good morning,

I have an issue with extracting the correct handicap value within the following table:

   K         L        M      
Handicap    York    Hereford
    0       1287     1280
    1       1285     1275
    2       1280     1271
    3       1275     1268
    4       1270     1265
    5       1268     1260
    6       1265     1258
    7       1260     1254
    8       1255     1250
    9       1253     1246

I also have these 2 lines of sample score/round data:

      G       H        I
    Round   Score   Handicap
     York   1269       5
     York   1270       4
   Hereford 1269      XXX
   Hereford 1270      XXX

If for instance someone on a York, gets a score of 1269, they should get a handicap of 5, which this formula achieves:

INDEX($K$7:$K$16,MATCH($H7,$L$7:$L$16,-1)) 1

However this formula only works on the one column $L$7:$L$16

Similarly, the 2ns score is calculated with the following formula:

=INDEX($K$8:$K$17,MATCH($H8,$L$8:$L$17,-1))

What I'd like to do is, build that out so if I changed the round to a Hereford, with the exact same score, the cell would automatically calculate that the handicap should be 3.

Is this possible, maybe with an array?

Regards, Andrew.

CodePudding user response：

With ms365, try:

Formula in I2:

=XLOOKUP(H2,FILTER(L$2:M$11,L$1:M$1=G2),K$2:K$11,"NB",-1,-1)

CodePudding user response：

I would avoid using OFFSET because it is a volatile function.

To select the appropriate column, you can use another MATCH:

MATCH($G7,$L$6:$M$6,0)

will return the column number. This makes it simple if you more than just York and Hereford columns.

Then, to return the matching line:

=MATCH($H7,INDEX($L$7:$M$16,0,MATCH($G7,$L$6:$M$6,0)),-1)

Note the use of 0 for the Row argument in the INDEX function which will return the entire column (all the rows).

Since your handicaps are sequential, as written this formula returns the same values as does yours. But I don't think it is correct since both formulas return 1 for a 1287 York.

You probably need to subtract one from the result of the formula.

=MATCH($H7,INDEX($L$7:$M$16,0,MATCH($G7,$L$6:$M$6,0)),-1)-1

CodePudding user response：

Reference your lookup range with an OFFSET() function, and for the third parameter (which is column offset), use a MATCH() on the headers.

The formula on your first row would be:

=INDEX($K$7:$K$16,MATCH(H7,OFFSET($L$7:$M$16,0,MATCH(G7,$L$6:$M$6,0)-1,ROWS($L$7:$M$16),1),-1)) 1