Formula to find the last number in a series
 

Hello,

I have the following list of number and need to find the last number that
starts with 1006? How do I do that? Also if I search for numbers beginning
with 1008 I need to know that there is none present.

1005001
1005002
1006001
1006002
1006003
1007001
1007002

Formula to find the last number in a series
 

Maybe this:

=LOOKUP(2,1/(LEFT(A1:A7,4)="1006"),A1:A7)

Also if I search for numbers beginning with 1008
I need to know that there is none present.

The above formula will return #N/A.


--
Biff
Microsoft Excel MVP


"OX_Gambit" wrote in message
...
Hello,

I have the following list of number and need to find the last number that
starts with 1006? How do I do that? Also if I search for numbers
beginning
with 1008 I need to know that there is none present.

1005001
1005002
1006001
1006002
1006003
1007001
1007002

Formula to find the last number in a series
 

Numbers in col J
=SUMPRODUCT((LEFT(TRIM($J$2:$J$22),4)="1006")*1)
if numbers in col L
=SUMPRODUCT((LEFT(TRIM($J$2:$J$22),4)=TEXT(L4,"000 0"))*1)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"OX_Gambit" wrote in message
...
Hello,

I have the following list of number and need to find the last number that
starts with 1006? How do I do that? Also if I search for numbers
beginning
with 1008 I need to know that there is none present.

1005001
1005002
1006001
1006002
1006003
1007001
1007002

Formula to find the last number in a series
 

Q1: use formula
=INDEX(A1:A10,MAX(IF(INT((A1:A10)/1000)=1006,ROW(A1:A10),0)))
As this is an array formula, commit it with CTRL+SHIFT+ENTER (not just
ENTER)
Change A1:A100 to suit your needs, for not use full column reference like
A:A unless you have Excel 2007

Q2: use array formula
=IF(MAX(IF(INT((A1:A10)/1000)=1008,ROW(A1:A10),0)),"found","not found")

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"OX_Gambit" wrote in message
...
Hello,

I have the following list of number and need to find the last number that
starts with 1006? How do I do that? Also if I search for numbers
beginning
with 1008 I need to know that there is none present.

1005001
1005002
1006001
1006002
1006003
1007001
1007002

Formula to find the last number in a series
 

OX_Gambit wrote:
Hello,

I have the following list of number and need to find the last number that
starts with 1006? How do I do that? Also if I search for numbers beginning
with 1008 I need to know that there is none present.

1005001
1005002
1006001
1006002
1006003
1007001
1007002


If, by "last number" you mean the highest number and they may not be sorted
ascending, try this array-entered formula:

=MAX((LEFT(A1:A7,4)="1006")*A1:A7)

This formula will result in 0 for "beginning with 1008" with your data.

Formula to find the last number in a series
 

What if the list of numbers are numbers not text?

"T. Valko" wrote:

Maybe this:

=LOOKUP(2,1/(LEFT(A1:A7,4)="1006"),A1:A7)

Also if I search for numbers beginning with 1008
I need to know that there is none present.

The above formula will return #N/A.


--
Biff
Microsoft Excel MVP


"OX_Gambit" wrote in message
...
Hello,

I have the following list of number and need to find the last number that
starts with 1006? How do I do that? Also if I search for numbers
beginning
with 1008 I need to know that there is none present.

1005001
1005002
1006001
1006002
1006003
1007001
1007002

Formula to find the last number in a series
 

What does the 1/(LEFT(A1:A7,4) do to the text?

"T. Valko" wrote:

Maybe this:

=LOOKUP(2,1/(LEFT(A1:A7,4)="1006"),A1:A7)

Also if I search for numbers beginning with 1008
I need to know that there is none present.

The above formula will return #N/A.


--
Biff
Microsoft Excel MVP


"OX_Gambit" wrote in message
...
Hello,

I have the following list of number and need to find the last number that
starts with 1006? How do I do that? Also if I search for numbers
beginning
with 1008 I need to know that there is none present.

1005001
1005002
1006001
1006002
1006003
1007001
1007002

Formula to find the last number in a series
 

OX_Gambit wrote...
What if the list of numbers are numbers not text?

Did you try the formula before deciding it couldn't work?

"T. Valko" wrote:
....
=LOOKUP(2,1/(LEFT(A1:A7,4)="1006"),A1:A7)
....

Formula to find the last number in a series
 

OX_Gambit wrote...
What does the 1/(LEFT(A1:A7,4) do to the text?
....

Unless Transition Formula Evaluation is enabled (it's disabled by
default), Excel converts numbers to text when used in text contexts,
such as when ranges are processed by the LEFT function. LEFT(range,4)
returns an array of the leftmost 4 characters from each cell in range.
In your case that would mean returning the leftmost 4 numerals as a
string. The entire expression actually was

1/(LEFT(A1:A7,4)="1006")

in which the leftmost 4 numerals in each cell in A1:A7 is compared to
"1006", the 4 digits you've said you're seeking. The result is an
array of TRUE (match) or FALSE (don't match) values. Next, Excel
converts TRUE to 1 and FALSE to 0 when they're used in arithmetic
contexts such as dividing them into 1. The entire expression will then
return an array of 1s (1/TRUE = 1/1 = 1) or #DIV/0! (1/FALSE = 1/0 =
#DIV/0!). The rest of the formula uses a quirk of Excel LOOKUP
function in which it will seek through it's entire 2nd argument to
find the first value greater than its 1st argument. It also keeps
track of the last value in its 2nd argument less than its 1st
argument. Since the 2nd argument would contain only 1 and #DIV/0!, it
won't find 2. It skips the #DIV/0! values, and it keeps track of the
last 1 found. When it runs through the 2nd argument array without
finding a 2, it uses the last 1 found as the match. That means it
matches the last cell in A1:A7 that begins with the ordered numerals
1006.