060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character, e.g.: "A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position of "A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character, e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
just relative position: yes if getting the row number, is what that means.

problems that arise, I believe are from actually doing work but some
functions only do half the job. e.g.: would think programming would have
allowed easy exclusion of top rows where having absolute cell references that
control that column have:
A, a, B.. as part of the settings; but then, the formula will not work.
I would have allowed programming to use: EXACT, ROW<(), then "" nothing...

- either that or it's just some trick I haven't quite worked out.
- or uniqueness of problems, haven't been thought of by others, ... thanks


"Biff" wrote:

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position of "A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character, e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

OK.....

If there's only one instance of "A":

=SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))

If there might be more than one instance of "A":

This will find the first instance:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
just relative position: yes if getting the row number, is what that
means.

problems that arise, I believe are from actually doing work but some
functions only do half the job. e.g.: would think programming would have
allowed easy exclusion of top rows where having absolute cell references
that
control that column have:
A, a, B.. as part of the settings; but then, the formula will not work.
I would have allowed programming to use: EXACT, ROW<(), then ""
nothing...

- either that or it's just some trick I haven't quite worked out.
- or uniqueness of problems, haven't been thought of by others, ...
thanks


"Biff" wrote:

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain
strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position of "A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character, e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to
represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

Hi, thanks.. sorry, many instances of each letter, so the 2nd example worked;

but wondering if way to exclude top rows, in case have cells with same
letter "A" as settings? trying to insert ROW5...

"Biff" wrote:

OK.....

If there's only one instance of "A":

=SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))

If there might be more than one instance of "A":

This will find the first instance:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
just relative position: yes if getting the row number, is what that
means.

problems that arise, I believe are from actually doing work but some
functions only do half the job. e.g.: would think programming would have
allowed easy exclusion of top rows where having absolute cell references
that
control that column have:
A, a, B.. as part of the settings; but then, the formula will not work.
I would have allowed programming to use: EXACT, ROW<(), then ""
nothing...

- either that or it's just some trick I haven't quite worked out.
- or uniqueness of problems, haven't been thought of by others, ...
thanks


"Biff" wrote:

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain
strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position of "A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character, e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to
represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

Just change the range to start at AE6:

For the RELATIVE row location:

=INDEX(ROW(AE6:AE1299)-ROW(AE6)+1),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))

For the ACTUAL row location:

=INDEX(ROW(AE6:AE1299),MATCH(TRUE,EXACT(AE6:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
Hi, thanks.. sorry, many instances of each letter, so the 2nd example
worked;

but wondering if way to exclude top rows, in case have cells with same
letter "A" as settings? trying to insert ROW5...

"Biff" wrote:

OK.....

If there's only one instance of "A":

=SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))

If there might be more than one instance of "A":

This will find the first instance:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299 ,"A"),0))

Biff

"nastech" wrote in message
...
hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
just relative position: yes if getting the row number, is what that
means.

problems that arise, I believe are from actually doing work but some
functions only do half the job. e.g.: would think programming would
have
allowed easy exclusion of top rows where having absolute cell
references
that
control that column have:
A, a, B.. as part of the settings; but then, the formula will not
work.
I would have allowed programming to use: EXACT, ROW<(), then ""
nothing...

- either that or it's just some trick I haven't quite worked out.
- or uniqueness of problems, haven't been thought of by others, ...
thanks


"Biff" wrote:

What kind of data do you have in your range?

Do the cells just contain the single letter "A" or do they contain
strings
like "Address" or "adult" ?

So, if you're using MATCH then you just want the relative position of
"A"
within the array AE1:AE1299 ?

Your posts always confuse me!

Biff

"nastech" wrote in message
...
060724 Match, Exact? NA error
hi, trying to make an "exact" match for the case, of a character,
e.g.:
"A"

where the following works, am trying to isolate capital "A"'s.
=MATCH("A",$AE$1:$AE$1299,0)

the following receives a NA error. Is there a correct way to
represent?
am open to inserting IF statements, EXACT, or ROW<=().. etc.
=MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


example of an EXACT, that was able to get to work in combo with
other:
=SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))