Help with LOOKUP (golf handicaps)

[GoLions]

Hello,
I have a problem that I've been trying to solve for days, and just
can't get it!! Perhaps someone here knows how I can do this with
Excel:

I'm using Excel to track golf handicaps for all members in our club. I
have successfully been able to calculate golf handicaps, and have them
automatically re-caculate whenever a new golf score is entered. The
resulting table looks like this: Golfer Names listed in Column A, and
beside each name in columns B, C, D, E, etc, are each person's running
handicaps. So Column B will show their handicap on day 1, column C
shows it on day 2, etc. The LAST handicap entry in each golfer's row is
their current handicap.

Since some golfer's don't play every day or every week (some play more
than other's), each golfer will have a different number of handicaps
entered..... BUT the LAST handicap entered is always that golfer's
current handicap. SO Golfer 1 may have their current handicap in column
C (if he's only played twice), while golfer 2 may have their current
handicap in column J (if he's played 9 times), etc.

My problem is that I cannot print out a single page that shows
everyone's CURRENT handicap. So I thought I'd insert a new column
beside everyone's name (column B) and have each golfer's current
handicap displayed in that column. Then I could just print out columns
A and B to get everyone's name and current handicap.

BUT I don't know how to do it! I can't just say "display the contents
of the xth column out", because the golfer's current handicap may be in
the 4th column, 10th column, 3rd column, etc.... it could be any
distance out from column A, depending on how many times the golfer has
played. I thought I could use the LOOKUP command, or DGET, but I can't
figure it out. Is there anyone that knows how I could do this in a
single worksheet?

One more piece of info that may help.... in the cells that do NOT have
a handicap entered, there is a formula that ends up displaying nothing
(i.e. "" if there's no handicap to display). I could make this display
an "X" or anything else if it helps.

SUMMARY:
In column B, I want to look out to the right in a given row until I see
the LAST entry of a handicap (i.e. the next cell displays nothing), and
then display that handicap in column B.

Anyone know how I can do that?

Many thanks

Rob.

 

[Tom Ogilvy]

=INDEX(C1:IV1,1,MATCH(9.99999999999999E+307,C1:IV1,1))

placed in B1, then drag fill down the column.

It will return an error if C:IV is empty, so you could test if there are any
numbers with count

=if(count(C1:IV1),INDEX(C1:IV1,1,MATCH(9.99999999999999E+307,C1:IV1,1)),"")

 

[John Wilson]

Tom,

I've seen formulas like this before in the ng (probably from you).
I can see that it works but I don't really understand how or why??
Could you elaborate a little??

John

=INDEX(C1:IV1,1,MATCH(9.99999999999999E+307,C1:IV1,1))

placed in B1, then drag fill down the column.

It will return an error if C:IV is empty, so you could test if there are any
numbers with count

=if(count(C1:IV1),INDEX(C1:IV1,1,MATCH(9.99999999999999E+307,C1:IV1,1)),"")

 

[Bob Phillips]

John,

9.99999999999999E+307 is the highest value that a cell can contain, so

MATCH(9.99999999999999E+307,C1:IV1,1)

is simply doing a match on the largest value that is less than or equal to
that amount. The INDEX just then gets the value.

Using 9.99999999999999E+307 was a technique first developed I believe by
Aladin Akyurek.

It seems unnecessarily obtuse to me, but if you do want to use it, it might
be best to get a workbook name, such as MaxValue, defined with that value,
and use the name in the formula.


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks

Tom,

I've seen formulas like this before in the ng (probably from you).
I can see that it works but I don't really understand how or why??
Could you elaborate a little??

John

=INDEX(C1:IV1,1,MATCH(9.99999999999999E+307,C1:IV1,1))

placed in B1, then drag fill down the column.

It will return an error if C:IV is empty, so you could test if there are any
numbers with count

=if(count(C1:IV1),INDEX(C1:IV1,1,MATCH(9.99999999999999E+307,C1:IV1,1)),"")

 

[Peo Sjoblom]

I also believe it is a bug since the help files says that the array should
be in ascending order and you would expect the #N/A error if not.
The problem is if MS fixes this "bug" and then it won't work. I'd rather
use something else.

 

[John Wilson]

Peo,

For the "something else", I found Leo Heuser's UDF in Google:
This at least makes sense.

=IF(ISERROR(lastentry(C1:IV1)),"",lastentry(C1:IV1))

Function LastEntry(Table As Range) As Variant
'(e-mail address removed), November 2000
Dim Col As Range
Dim Cell As Range
Dim ColumnToGet As Range

For Each Col In Table.Columns
If Application.CountA(Col) <> 0 Then
Set ColumnToGet = Col
End If
Next Col
For Each Cell In ColumnToGet.Cells
If Cell.Value Then LastEntry = Cell.Value
Next Cell

Set Col = Nothing
Set Cell = Nothing
Set ColumnToGet = Nothing
End Function

John

 

[John Wilson]

Bob,

Maybe I'm just being dense today, but I still don't get it??

is simply doing a match on the largest value that is less than or equal to
that amount. The INDEX just then gets the value.

If I have say 1,12,14,4,3 across a row, the "largest value" is 14
but the formula will return "3" (the "rightmost" value).

John

 

[Bob Phillips]

John,

You're right, it is not the largest value, but the last value.

Another worksheet alternative is

=INDIRECT(CHAR(MAX(IF(NOT(ISBLANK(C1:IV1)),COLUMN(C1:IV1)))+64)&ROW())

which is an array form ula

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Aladin Akyurek]

I also believe it is a bug since the help files says that the array should
be in ascending order and you would expect the #N/A error if not.
The problem is if MS fixes this "bug" and then it won't work. I'd rather
use something else.

I fail to understand how the following proposition/argument forces one to
conclude a bug:

"[T]he help files says that the array should be in ascending order and you
would expect the #N/A error if not."

It seems to me quite probable that the lookup functions in Excel share the
same procedures. It's quite plausible that Excel's code contain the
following procedures:

[A] Linear/sequential search (LS).
Binary search (BS).
[C] Used range (UR).

It's also quite certain that

=MATCH(v,X,1)
=VLOOKUP(v,Y,n,1)

bound to activate UR and BS. I admit to be less certain about UR, although I
believe the behavior of the lookup functions strongly suggest it.
Additionally, this is of capital importance, these functions do not call
upon some procedure which verifies whether X or INDEX(Y,0,1) is sorted in
ascending order. That such a check/test is NOT carried out is certain for an
obvious reason: Speed. MATCH et al would not be as fast as we know them when
such a test should be included. I'm happy to observe that the programmers at
MS did not include such a test.

I strongly reject an application developer's view that Excel itself should
check whether a user is not passing an unsorted object (array or range) to
these functions. The article
http://support.microsoft.com/default.aspx?scid=kb;en-us;181201 warns against
unintended use of match-type (range-lookup) TRUE/1 in lookup functions, not
revealing a bug about them, as sometimes pointed out.

Now back to the use of BigNum (9.99999999999999E+307) and BigStr
(REPT("z",255)...

BigNum and BigStr are extremely rare events in data Excel is used to
process. That's why it's safe to use them as lookup values in an expression
that will invoke BS. Schematically,

=MATCH(BigNum/BigStr,X,1)

will invoke:

BS(BigNum/BigStr,UR(X))

Given the foregoing, we are to bound to get the last value in X because the
last value will be the last half of data BS will be examining. Since the
last examined value <= BigNum/BigStr, MATCH (or ther lookup functions) will
return it tout court.

Hope it's clear that only an *ascending order check* would allow Excel to
return #N/A regarding the formulas under discussion. A software programmer
should definitely reject adding a procedure for such a test to the
underlying system for performance reasons.

 

[Aladin Akyurek]

If you apply binary search (a procedure invoked by the match-type set to 1)
to these numbers, the last value that will be examined is 3. And since
3<=BigNum, we get 3 as the retrieval result.

 

[Tushar Mehta]

It relies on the fact that when the last argument is 1 MATCH fails to
ensure that the array is in ascending order and proceeds with a binary
search to find the largest number not greater than the specified value.

Personally, I'd stick with OFFSET. In B2, enter
=OFFSET(C2,0,COUNTA(C2:IV2)-1,1,1)

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Tom,

I've seen formulas like this before in the ng (probably from you).
I can see that it works but I don't really understand how or why??
Could you elaborate a little??

John

 

[Aladin Akyurek]

It relies on the fact that when the last argument is 1 MATCH fails to
ensure that the array is in ascending order and proceeds with a binary
search to find the largest number not greater than the specified value.

Personally, I'd stick with OFFSET. In B2, enter
=OFFSET(C2,0,COUNTA(C2:IV2)-1,1,1)

MATCH does not fail "to ensure that the array is in ascending order" at all
for it does NOT check whether the array is in ascending order. When the
match-type is 1, MATCH immediately proceeds with a binary search, as it
should.

You're not sticking with OFFSET, rather with COUNTA which will fail
miserably when there are empty cells in between the cells with values.

 

[Tushar Mehta]

Hope it's clear that only an *ascending order check* would allow Excel to
return #N/A regarding the formulas under discussion. A software programmer
should definitely reject adding a procedure for such a test to the
underlying system for performance reasons.

Sorry, Aladin, but Garbage In Garbage Out as a programming methodology
hasn't been in vogue for some decades ago.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I also believe it is a bug since the help files says that the array should
be in ascending order and you would expect the #N/A error if not.
The problem is if MS fixes this "bug" and then it won't work. I'd rather
use something else.

I fail to understand how the following proposition/argument forces one to
conclude a bug:

"[T]he help files says that the array should be in ascending order and you
would expect the #N/A error if not."

It seems to me quite probable that the lookup functions in Excel share the
same procedures. It's quite plausible that Excel's code contain the
following procedures:

[A] Linear/sequential search (LS).
Binary search (BS).
[C] Used range (UR).

It's also quite certain that

=MATCH(v,X,1)
=VLOOKUP(v,Y,n,1)

bound to activate UR and BS. I admit to be less certain about UR, although I
believe the behavior of the lookup functions strongly suggest it.
Additionally, this is of capital importance, these functions do not call
upon some procedure which verifies whether X or INDEX(Y,0,1) is sorted in
ascending order. That such a check/test is NOT carried out is certain for an
obvious reason: Speed. MATCH et al would not be as fast as we know them when
such a test should be included. I'm happy to observe that the programmers at
MS did not include such a test.

I strongly reject an application developer's view that Excel itself should
check whether a user is not passing an unsorted object (array or range) to
these functions. The article
http://support.microsoft.com/default.aspx?scid=kb;en-us;181201 warns against
unintended use of match-type (range-lookup) TRUE/1 in lookup functions, not
revealing a bug about them, as sometimes pointed out.

Now back to the use of BigNum (9.99999999999999E+307) and BigStr
(REPT("z",255)...

BigNum and BigStr are extremely rare events in data Excel is used to
process. That's why it's safe to use them as lookup values in an expression
that will invoke BS. Schematically,

=MATCH(BigNum/BigStr,X,1)

will invoke:

BS(BigNum/BigStr,UR(X))

Given the foregoing, we are to bound to get the last value in X because the
last value will be the last half of data BS will be examining. Since the
last examined value <= BigNum/BigStr, MATCH (or ther lookup functions) will
return it tout court.

Hope it's clear that only an *ascending order check* would allow Excel to
return #N/A regarding the formulas under discussion. A software programmer
should definitely reject adding a procedure for such a test to the
underlying system for performance reasons.

 

[Aladin Akyurek]

Sorry, Aladin, but Garbage In Garbage Out as a programming methodology
hasn't been in vogue for some decades ago.

Why don't you spell out what the underlying procedures would be, since you
consider the current set (as I construe) to be GIGO?

I'll do it for you...

Given

=MATCH(v,X,1)

invoke:

If match-type = 1
arr = QSORT(UR(X),Ascending)
if X = arr, BS(v,UR(X))
else #N/A
else...

instead of:

If match-type = 1
BS(v,UR(X))
elese...

Maybe you could indicate the respective costs of the foregoing according to
the "programming methodology" you approve.

Hope it's clear that only an *ascending order check* would allow Excel to
return #N/A regarding the formulas under discussion. A software programmer
should definitely reject adding a procedure for such a test to the
underlying system for performance reasons.

Sorry, Aladin, but Garbage In Garbage Out as a programming methodology
hasn't been in vogue for some decades ago.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I also believe it is a bug since the help files says that the array should
be in ascending order and you would expect the #N/A error if not.
The problem is if MS fixes this "bug" and then it won't work. I'd rather
use something else.

I fail to understand how the following proposition/argument forces one to
conclude a bug:

"[T]he help files says that the array should be in ascending order and you
would expect the #N/A error if not."

It seems to me quite probable that the lookup functions in Excel share the
same procedures. It's quite plausible that Excel's code contain the
following procedures:

[A] Linear/sequential search (LS).
Binary search (BS).
[C] Used range (UR).

It's also quite certain that

=MATCH(v,X,1)
=VLOOKUP(v,Y,n,1)

bound to activate UR and BS. I admit to be less certain about UR, although I
believe the behavior of the lookup functions strongly suggest it.
Additionally, this is of capital importance, these functions do not call
upon some procedure which verifies whether X or INDEX(Y,0,1) is sorted in
ascending order. That such a check/test is NOT carried out is certain for an
obvious reason: Speed. MATCH et al would not be as fast as we know them when
such a test should be included. I'm happy to observe that the programmers at
MS did not include such a test.

I strongly reject an application developer's view that Excel itself should
check whether a user is not passing an unsorted object (array or range) to
these functions. The article
http://support.microsoft.com/default.aspx?scid=kb;en-us;181201 warns against
unintended use of match-type (range-lookup) TRUE/1 in lookup functions, not
revealing a bug about them, as sometimes pointed out.

Now back to the use of BigNum (9.99999999999999E+307) and BigStr
(REPT("z",255)...

BigNum and BigStr are extremely rare events in data Excel is used to
process. That's why it's safe to use them as lookup values in an expression
that will invoke BS. Schematically,

=MATCH(BigNum/BigStr,X,1)

will invoke:

BS(BigNum/BigStr,UR(X))

Given the foregoing, we are to bound to get the last value in X because the
last value will be the last half of data BS will be examining. Since the
last examined value <= BigNum/BigStr, MATCH (or ther lookup functions) will
return it tout court.

Hope it's clear that only an *ascending order check* would allow Excel to
return #N/A regarding the formulas under discussion. A software programmer
should definitely reject adding a procedure for such a test to the
underlying system for performance reasons.

 

[Tom Ogilvy]

John,

As Aladin has explained (and he is the first I saw use this method), this
method uses the predicatable and consistent behavior of the match function
to return the position of the last used cell in the match range. It searches
for an exact match, and if not found, takes the last cell that would hold a
value less than the attempted match value; the cell that would preceed the
exact match if the data were sorted. Since we are looking for the largest
possible number, this has to be the cell after the last filled cell; so the
last filled cell position is returned. It has the advantage of working with
blank cells embedded within the filled cells. Aladin explainst that this is
predicatable behavior of binary search.

for a group that is concerned with -- vice *1 and using multiple arguments
vice multiplication, as a speed differential, using sumproduct with a single
argument, using Sumif to do a 3D conditional sum, Using arrays in the
arguments of Countif and so forth, I am surprised at the hesitancy to use
"discovered" behavior.

--
Regards,
Tom Ogilvy

Tom,

I've seen formulas like this before in the ng (probably from you).
I can see that it works but I don't really understand how or why??
Could you elaborate a little??

John

=INDEX(C1:IV1,1,MATCH(9.99999999999999E+307,C1:IV1,1))

placed in B1, then drag fill down the column.

It will return an error if C:IV is empty, so you could test if there are any
numbers with count

=if(count(C1:IV1),INDEX(C1:IV1,1,MATCH(9.99999999999999E+307,C1:IV1,1)),"")

 

[Tushar Mehta]

{shrug}

You want to do the wrong thing fast, be my guest.

--
Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Sorry, Aladin, but Garbage In Garbage Out as a programming methodology
hasn't been in vogue for some decades ago.

Why don't you spell out what the underlying procedures would be, since you
consider the current set (as I construe) to be GIGO?

I'll do it for you...

Given

=MATCH(v,X,1)

invoke:

If match-type = 1
arr = QSORT(UR(X),Ascending)
if X = arr, BS(v,UR(X))
else #N/A
else...

instead of:

If match-type = 1
BS(v,UR(X))
elese...

Maybe you could indicate the respective costs of the foregoing according to
the "programming methodology" you approve.

Hope it's clear that only an *ascending order check* would allow Excel to
return #N/A regarding the formulas under discussion. A software programmer
should definitely reject adding a procedure for such a test to the
underlying system for performance reasons.

Sorry, Aladin, but Garbage In Garbage Out as a programming methodology
hasn't been in vogue for some decades ago.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I also believe it is a bug since the help files says that the array should
be in ascending order and you would expect the #N/A error if not.
The problem is if MS fixes this "bug" and then it won't work. I'd rather
use something else.

I fail to understand how the following proposition/argument forces one to
conclude a bug:

"[T]he help files says that the array should be in ascending order and you
would expect the #N/A error if not."

It seems to me quite probable that the lookup functions in Excel share the
same procedures. It's quite plausible that Excel's code contain the
following procedures:

[A] Linear/sequential search (LS).
Binary search (BS).
[C] Used range (UR).

It's also quite certain that

=MATCH(v,X,1)
=VLOOKUP(v,Y,n,1)

bound to activate UR and BS. I admit to be less certain about UR, although I
believe the behavior of the lookup functions strongly suggest it.
Additionally, this is of capital importance, these functions do not call
upon some procedure which verifies whether X or INDEX(Y,0,1) is sorted in
ascending order. That such a check/test is NOT carried out is certain for an
obvious reason: Speed. MATCH et al would not be as fast as we know them when
such a test should be included. I'm happy to observe that the programmers at
MS did not include such a test.

I strongly reject an application developer's view that Excel itself should
check whether a user is not passing an unsorted object (array or range) to
these functions. The article
http://support.microsoft.com/default.aspx?scid=kb;en-us;181201 warns against
unintended use of match-type (range-lookup) TRUE/1 in lookup functions, not
revealing a bug about them, as sometimes pointed out.

Now back to the use of BigNum (9.99999999999999E+307) and BigStr
(REPT("z",255)...

BigNum and BigStr are extremely rare events in data Excel is used to
process. That's why it's safe to use them as lookup values in an expression
that will invoke BS. Schematically,

=MATCH(BigNum/BigStr,X,1)

will invoke:

BS(BigNum/BigStr,UR(X))

Given the foregoing, we are to bound to get the last value in X because the
last value will be the last half of data BS will be examining. Since the
last examined value <= BigNum/BigStr, MATCH (or ther lookup functions) will
return it tout court.

Hope it's clear that only an *ascending order check* would allow Excel to
return #N/A regarding the formulas under discussion. A software programmer
should definitely reject adding a procedure for such a test to the
underlying system for performance reasons.

 

[Tushar Mehta]

If you want to get into a discussion of what doesn't work, what happens
to MATCH if the last element is an error? Or a string?

As I pointed out in an earlier discussion, the *only* surefire way find
the last entry in a row or column is to use a UDF. XL by itself
doesn't support that functionality.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Aladin Akyurek]

If you want to get into a discussion of what doesn't work, what happens

to MATCH if the last element is an error? Or a string?

I can't believe you come with this... The formula with BigNum is used if one
is interested in the last numeric value and the one with BigStr is used if
one is in the last text value...

Given that X is

{#N/A;"",1;"Bob";#DIV/0!}

where "" stands for an empty cell,

we get:

=LOOKUP(BigNum,X) =====> 1
=MATCH(BigNum,X) =====> 3
=LOOKUP(BigStr,X) =====> Bob
=MATCH(BigStr,X) =====> 4

In fact, I see questions more often about the last numeric or text value in
a range where computations take place with occasional errors, rather than
about just any value.

If one is however interested in *ANY* last value (including any error value
or logical value), one should apply something different, not these formulas.
But then you already knew all this... So, I don't understand why you try to
replace the last numeric value or the last text value into "the last
entry"...

As I pointed out in an earlier discussion, the *only* surefire way find
the last entry in a row or column is to use a UDF. XL by itself
doesn't support that functionality.

If one is only interested in the last numeric/text value in a range of
formula results, this UDF will be surefire the wrong thing to resort to.

 

[Harlan Grove]

If you want to get into a discussion of what doesn't work, what happens
to MATCH if the last element is an error? Or a string?

Or, perhaps more to the point, when Microsoft makes undocumented changes to
MATCH in some future version. Not exactly a remote possibility.

As I pointed out in an earlier discussion, the *only* surefire way find
the last entry in a row or column is to use a UDF. XL by itself
doesn't support that functionality.

Wrong. Even if you don't like Aladin's shortcut, brute force doesn't require
a UDF. An array formula like

=MAX(1-ISBLANK(A1),(1-ISBLANK(A2:A65536))*ROW(A2:A65536))

would work just fine as long as you accept that it'll return 0 rather than
#N/A if the column is entirely blank.

 

[Tushar Mehta]

LOL!

for a group that is concerned with -- vice *1 and using multiple arguments
vice multiplication, as a speed differential, using sumproduct with a single
argument, using Sumif to do a 3D conditional sum, Using arrays in the
arguments of Countif and so forth, I am surprised at the hesitancy to use
"discovered" behavior.

Hi Tom,

Innocent of all charges. You'll be hard pressed to find any
contributions from me on any of those 'speed' issues. I'm perfectly
happy to use N() or 0+ or whatever if it aids readability and
maintainability.


--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions