Re: Search,Index,Match help

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 08/31/04 

Date: Tue, 31 Aug 2004 20:45:18 +0200

Hi
not really sure what you're trying to do. Maybe post some example rows
of your data (plain text please) 

--
Regards
Frank Kabel
Frankfurt, Germany
"GerryK" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag
news:019b01c48f87$90aed3b0$a401280a@phx.gbl...
> Thanks Frank,
> I have to enter a text string in C2 therefore I'd like a
> similar formula to be placed along side my list of codes
> that tells me what is associated with the data. The latter
> formula (first post)works great as I can see the progress
> of code use but I'd like to see the same thing to compare
> for the text string entered into C2
> I'm working on your idea but also cannot get my 'blended'
> formula to work.
>
> =INDEX($B$3:$B$9999,MATCH(0,COUNTIF('Sheet1'!
> $J$8:$J8,SUMPRODUCT(--(ISNUMBER(SEARCH(C2,R3:R9999)))))))
>
> Is this off base?
>
> >-----Original Message-----
> >Hi
> >why not place your arry formula in C2, copy it down and
> then use the
> >SUMPRODUCT formula.
> >
> >BUT you may also consider using a pivot table for this
> (would do this
> >all in one step)
> >
> >--
> >Regards
> >Frank Kabel
> >Frankfurt, Germany
> >
> >"GerryK" <anonymous@discussions.microsoft.com> schrieb im
> Newsbeitrag
> >news:386a01c48f75$d59b3200$a601280a@phx.gbl...
> >> Hi,
> >> My situation is that I'd like to enter a text string in
> C2
> >> and find out how many task codes from column B appear
> >> associated with the entered string at C2.
> >>
> >> This formula searches for my input text string, then
> >> totals from column J.
> >> SUMPRODUCT(--(ISNUMBER(SEARCH(C2,R3:R9999))),J3:J9999)
> >>
> >> This formula (array entered and copied down) gives me an
> >> incremental list of used task codes (there are only 8
> >> codes possible) from the entire column B. List produced
> >> identifies that a code was used only, and as the records
> >> increase so does the list until all 8 are displayed.
> >> INDEX($B$3:$B$9999,MATCH(0,COUNTIF
> >> ($I$8:$I8,$B$3:$B$9999),0))
> >>
> >> Is there someway to merge the search concept with a
> unique
> >> listing returned from B?
> >>
> >> TIA and thanks to FK for help so far!
> >>
> >
> >.
> >
Quantcast