Re: Multiple corresponding Values (Biff.........)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Bob, I found the problem.......I had some #NUM's after my info so I think the
formula was looking to that. When I changed the range the formula worked.
However the range of values will always be changing and I dont want to have
to change the formula range every time. Is there a way I can get the formula
to ignore the #NUM??

"Bob Phillips" wrote:

I think that this is what you need

=INDEX($A$98:$AB$400,SMALL(IF($C$98:$C$400=$H$90,ROW($98:$400)),ROW(1:1))-MI
N(ROW($A$98:$A$400))+1,1)

still an array formula

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ribeye" <Ribeye@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4927EF4D-E15E-4BF3-924B-B09767A4F0AC@xxxxxxxxxxxxxxxx
Im trying to pull all the rows that match a certain criteria. I already
have
one of these formulas in place that pulls from another sheet but the range
is
1:16200 and it works great. Here's a small quick layout:

NY N Apt
NY N Apt
NY N Off
NY N Off
NY N Ret

where NY is in A98, N in B98 and Apt in C98. I want all the rows that
apply
to Apt.

"Bob Phillips" wrote:

I think the problem is in the Row(98:98), which is looking for the 98th
smallest value, which seems unlikely.

What exactly are you trying to do?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ribeye" <Ribeye@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D420515B-3D86-4E41-BB4F-1A82DA32EE43@xxxxxxxxxxxxxxxx
Having trouble getting this formula to work. Im hoping its just
something
small I'm missing.....My info is in A98:AB400 the values I want to
evaluate
are in column C.
Here is my current formula:

=INDEX($A98:$AB400,SMALL(IF($C$98:$C$400=H6,ROW($98:$400)),ROW(98:98)),1)
I've tried to change the ranges, the rows and everthing else and I
still
get
error formulas........Please help to end my suffering......






.

Relevant Pages

  • Re: looping thru each w/sheet
    ... > I think Bob wanted: ... >> Bob Phillips ... >> (remove nothere from email address if mailing direct) ...
    (microsoft.public.excel.programming)
  • Re: Adding a date timestamp to the last column of a spreadsheet
    ... Bob Phillips wrote: ... If sId> 5 Then ... extract data from a spreadsheet selected by a user, ...
    (microsoft.public.excel.programming)
  • Re: Multiple corresponding Values (Biff.........)
    ... Bob, I copied the exact formula over and used the array but I am still ... "Bob Phillips" wrote: ... NY N Apt ...
    (microsoft.public.excel.programming)
  • Re: Opening a file with date extension
    ... Many thanks Bob your code worked a treat. ... "Bob Phillips" wrote: ... (remove xxx from email address if mailing direct) ... I have a workbook that opens a file, imports data and closes it down ...
    (microsoft.public.excel.programming)
  • Re: Counting duplicates
    ... Yes sorry Bob i can see i am writing in a language only i seem to know!! ... "Bob Phillips" wrote: ... (replace somewhere in email address with gmail if mailing direct) ...
    (microsoft.public.excel.misc)