Match, Exact? NA error
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")))
|