I have a column of values that contain text like XX-YY, ZZZ-DD4, etc. I want
to find the last row of the column that has a "-". Is there a function I can
use to find this (maybe SUMPRODUCT ?). Thanks for any help with this.

To return the last value containing "-", try...

=LOOKUP(9.99999999999999E+307,FIND("-",A2:A100),A2:A100)

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
dhstein wrote:

I have a column of values that contain text like XX-YY, ZZZ-DD4, etc. I want
to find the last row of the column that has a "-". Is there a function I can
use to find this (maybe SUMPRODUCT ?). Thanks for any help with this.

=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)


"dhstein" wrote:

I have a column of values that contain text like XX-YY, ZZZ-DD4, etc. I want
to find the last row of the column that has a "-". Is there a function I can
use to find this (maybe SUMPRODUCT ?). Thanks for any help with this.

Thanks teethless and Domenic. That's great - but I don't want the value - I
want the row number. How could I get that ?



"Teethless mama" wrote:

=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)


"dhstein" wrote:

I have a column of values that contain text like XX-YY, ZZZ-DD4, etc. I want
to find the last row of the column that has a "-". Is there a function I can
use to find this (maybe SUMPRODUCT ?). Thanks for any help with this.

Thanks - used "MATCH" function and your function to get the last row - thanks
again



"Teethless mama" wrote:

=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)


"dhstein" wrote:

I have a column of values that contain text like XX-YY, ZZZ-DD4, etc. I want
to find the last row of the column that has a "-". Is there a function I can
use to find this (maybe SUMPRODUCT ?). Thanks for any help with this.

You're Welcome!


"dhstein" wrote:

Thanks - used "MATCH" function and your function to get the last row - thanks
again



"Teethless mama" wrote:

=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)


"dhstein" wrote:

I have a column of values that contain text like XX-YY, ZZZ-DD4, etc. I want
to find the last row of the column that has a "-". Is there a function I can
use to find this (maybe SUMPRODUCT ?). Thanks for any help with this.

Try...

=LOOKUP(9.99999999999999E+307,FIND("-",A2:A100),ROW(A2:A100))

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
dhstein wrote:

Thanks teethless and Domenic. That's great - but I don't want the value - I
want the row number. How could I get that ?



"Teethless mama" wrote:

=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)


"dhstein" wrote:

I have a column of values that contain text like XX-YY, ZZZ-DD4, etc. I
want
to find the last row of the column that has a "-". Is there a function I
can
use to find this (maybe SUMPRODUCT ?). Thanks for any help with this.

=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)

No need for the ISNUMBER function. You can't reference the entire column in
the FIND function if you're using a version of Excel prior to Excel 2007.

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)


"dhstein" wrote:

I have a column of values that contain text like XX-YY, ZZZ-DD4, etc. I
want
to find the last row of the column that has a "-". Is there a function I
can
use to find this (maybe SUMPRODUCT ?). Thanks for any help with this.

Thanks for this information. I thought I knew every Excel function - but
Lookup was new to me. I tried to read the examples, but I'm not clear how
this LOOKUP function is working - I even did "evaluate function" - but still
not sure. What is the "1/" . I assume somehow we're getting errors of
divide by zero when FIND is not found. I would appreciate it if one of you
could explain it a little. Thanks.

David

"T. Valko" wrote:

=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)

No need for the ISNUMBER function. You can't reference the entire column in
the FIND function if you're using a version of Excel prior to Excel 2007.

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)


"dhstein" wrote:

I have a column of values that contain text like XX-YY, ZZZ-DD4, etc. I
want
to find the last row of the column that has a "-". Is there a function I
can
use to find this (maybe SUMPRODUCT ?). Thanks for any help with this.

What is the "1/" . I assume somehow we're getting
errors of divide by zero when FIND is not found.

Yes, that is correct depending on which version of the formula you use. If
you use the 1/ISNUMBER version then you'll get #DIV/0! errors. If you use
the 1/FIND version then you'll get #VALUE! errors.

Here's how it works...

...........A.....
1....xx-xx
2...10
3...aa-bb
4...yes-no
5...dddd

=LOOKUP(2,1/FIND("-",A1:A5),A1:A5)

Result = yes-no

FIND returns the starting position of the character we're looking for in the
string. If the character is not found then the result is the error #VALUE!.

FIND("-",A1:A5)

FIND("-",A1) = 3 (the dash is found at the 3rd character)
FIND("-",A2) = #VALUE! (the dash is not found)
FIND("-",A3) = 3 (the dash is found at the 3rd character)
FIND("-",A4) = 4 (the dash is found at the 4th character)
FIND("-",A5) = #VALUE! (the dash is not found)

Each of these results is then divided:

1/FIND("-",A1:A5)

1/3 = 0.333
1/#VALUE! = #VALUE!
1/3 = 0.333
1/4 = 0.250
1/#VALUE! = #VALUE!

The way that LOOKUP works is if the lookup_value 2 is greater than all the
values in the lookup_vector it will match the *last* value in the
lookup_vector that is less than the lookup_value 2.

The *last* value in the lookup_vector that is less than the lookup_value 2
is
0.250. The #VALUE!! errors are ignored. We use a lookup_value of 2
because we know that the result of this expression:

1/FIND("-",A1:A5)

will not return a value greater than 1 thus ensuring that the lookup_value 2
is guaranteed to be greater than any value in the lookup_vector.

The final result of the formula is the value in the result_vector A1:A5 that
corresponds to the *last* value in the lookup_vector that is less than 2.

Lookup_vector...Result_vector
.......0.333................xx-xx
....#VALUE!............10
.......0.333................aa-bb
.......0.250................yes-no
....#VALUE!............dddd

So:

=LOOKUP(2,1/FIND("-",A1:A5),A1:A5)

Result = yes-no



exp101
--
Biff
Microsoft Excel MVP


"dhstein" wrote in message
...

Thanks for this information. I thought I knew every Excel function - but
Lookup was new to me. I tried to read the examples, but I'm not clear how
this LOOKUP function is working - I even did "evaluate function" - but
still
not sure. What is the "1/" . I assume somehow we're getting errors of
divide by zero when FIND is not found. I would appreciate it if one of
you
could explain it a little. Thanks.

David

"T. Valko" wrote:

=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)

No need for the ISNUMBER function. You can't reference the entire column
in
the FIND function if you're using a version of Excel prior to Excel 2007.

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)


"dhstein" wrote:

I have a column of values that contain text like XX-YY, ZZZ-DD4, etc.
I
want
to find the last row of the column that has a "-". Is there a
function I
can
use to find this (maybe SUMPRODUCT ?). Thanks for any help with
this.

Biff,

Thanks for taking the time to explain this.

David


"T. Valko" wrote:

What is the "1/" . I assume somehow we're getting
errors of divide by zero when FIND is not found.

Yes, that is correct depending on which version of the formula you use. If
you use the 1/ISNUMBER version then you'll get #DIV/0! errors. If you use
the 1/FIND version then you'll get #VALUE! errors.

Here's how it works...

...........A.....
1....xx-xx
2...10
3...aa-bb
4...yes-no
5...dddd

=LOOKUP(2,1/FIND("-",A1:A5),A1:A5)

Result = yes-no

FIND returns the starting position of the character we're looking for in the
string. If the character is not found then the result is the error #VALUE!.

FIND("-",A1:A5)

FIND("-",A1) = 3 (the dash is found at the 3rd character)
FIND("-",A2) = #VALUE! (the dash is not found)
FIND("-",A3) = 3 (the dash is found at the 3rd character)
FIND("-",A4) = 4 (the dash is found at the 4th character)
FIND("-",A5) = #VALUE! (the dash is not found)

Each of these results is then divided:

1/FIND("-",A1:A5)

1/3 = 0.333
1/#VALUE! = #VALUE!
1/3 = 0.333
1/4 = 0.250
1/#VALUE! = #VALUE!

The way that LOOKUP works is if the lookup_value 2 is greater than all the
values in the lookup_vector it will match the *last* value in the
lookup_vector that is less than the lookup_value 2.

The *last* value in the lookup_vector that is less than the lookup_value 2
is
0.250. The #VALUE!! errors are ignored. We use a lookup_value of 2
because we know that the result of this expression:

1/FIND("-",A1:A5)

will not return a value greater than 1 thus ensuring that the lookup_value 2
is guaranteed to be greater than any value in the lookup_vector.

The final result of the formula is the value in the result_vector A1:A5 that
corresponds to the *last* value in the lookup_vector that is less than 2.

Lookup_vector...Result_vector
.......0.333................xx-xx
....#VALUE!............10
.......0.333................aa-bb
.......0.250................yes-no
....#VALUE!............dddd

So:

=LOOKUP(2,1/FIND("-",A1:A5),A1:A5)

Result = yes-no



exp101
--
Biff
Microsoft Excel MVP


"dhstein" wrote in message
...

Thanks for this information. I thought I knew every Excel function - but
Lookup was new to me. I tried to read the examples, but I'm not clear how
this LOOKUP function is working - I even did "evaluate function" - but
still
not sure. What is the "1/" . I assume somehow we're getting errors of
divide by zero when FIND is not found. I would appreciate it if one of
you
could explain it a little. Thanks.

David

"T. Valko" wrote:

=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)

No need for the ISNUMBER function. You can't reference the entire column
in
the FIND function if you're using a version of Excel prior to Excel 2007.

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)


"dhstein" wrote:

I have a column of values that contain text like XX-YY, ZZZ-DD4, etc.
I
want
to find the last row of the column that has a "-". Is there a
function I
can
use to find this (maybe SUMPRODUCT ?). Thanks for any help with
this.

You're welcome!

--
Biff
Microsoft Excel MVP


"dhstein" wrote in message
...
Biff,

Thanks for taking the time to explain this.

David


"T. Valko" wrote:

What is the "1/" . I assume somehow we're getting
errors of divide by zero when FIND is not found.

Yes, that is correct depending on which version of the formula you use.
If
you use the 1/ISNUMBER version then you'll get #DIV/0! errors. If you use
the 1/FIND version then you'll get #VALUE! errors.

Here's how it works...

...........A.....
1....xx-xx
2...10
3...aa-bb
4...yes-no
5...dddd

=LOOKUP(2,1/FIND("-",A1:A5),A1:A5)

Result = yes-no

FIND returns the starting position of the character we're looking for in
the
string. If the character is not found then the result is the error
#VALUE!.

FIND("-",A1:A5)

FIND("-",A1) = 3 (the dash is found at the 3rd character)
FIND("-",A2) = #VALUE! (the dash is not found)
FIND("-",A3) = 3 (the dash is found at the 3rd character)
FIND("-",A4) = 4 (the dash is found at the 4th character)
FIND("-",A5) = #VALUE! (the dash is not found)

Each of these results is then divided:

1/FIND("-",A1:A5)

1/3 = 0.333
1/#VALUE! = #VALUE!
1/3 = 0.333
1/4 = 0.250
1/#VALUE! = #VALUE!

The way that LOOKUP works is if the lookup_value 2 is greater than all
the
values in the lookup_vector it will match the *last* value in the
lookup_vector that is less than the lookup_value 2.

The *last* value in the lookup_vector that is less than the lookup_value
2
is
0.250. The #VALUE!! errors are ignored. We use a lookup_value of 2
because we know that the result of this expression:

1/FIND("-",A1:A5)

will not return a value greater than 1 thus ensuring that the
lookup_value 2
is guaranteed to be greater than any value in the lookup_vector.

The final result of the formula is the value in the result_vector A1:A5
that
corresponds to the *last* value in the lookup_vector that is less than 2.

Lookup_vector...Result_vector
.......0.333................xx-xx
....#VALUE!............10
.......0.333................aa-bb
.......0.250................yes-no
....#VALUE!............dddd

So:

=LOOKUP(2,1/FIND("-",A1:A5),A1:A5)

Result = yes-no



exp101
--
Biff
Microsoft Excel MVP


"dhstein" wrote in message
...

Thanks for this information. I thought I knew every Excel function -
but
Lookup was new to me. I tried to read the examples, but I'm not clear
how
this LOOKUP function is working - I even did "evaluate function" - but
still
not sure. What is the "1/" . I assume somehow we're getting errors of
divide by zero when FIND is not found. I would appreciate it if one of
you
could explain it a little. Thanks.

David

"T. Valko" wrote:

=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)

No need for the ISNUMBER function. You can't reference the entire
column
in
the FIND function if you're using a version of Excel prior to Excel
2007.

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)


"dhstein" wrote:

I have a column of values that contain text like XX-YY, ZZZ-DD4,
etc.
I
want
to find the last row of the column that has a "-". Is there a
function I
can
use to find this (maybe SUMPRODUCT ?). Thanks for any help with
this.