# search for text in colB, starting with next row search for 1st occurrence of specific #

1. ## search for text in colB, starting with next row search for 1st occurrence of specific #

I want to search for DG in colB, then beginning with the next row search for the first occurrence of 123 in colA and then return 456 in colC.

I've tried various sumproduct formulae with no luck, i.e. =SUMPRODUCT(--(B5:B200="DG"),--(A5:A200=165),--C5:C200)

Here's my sample data:

colA----colB-----colC
---------DF
106--------------229
123--------------444
---------DG
117--------------678
123--------------456
152--------------100
286--------------750
123--------------111
---------KL
123--------------677

Thanks,
roothog

2. ## Re: search for text in colB, starting with next row search for 1st occurrence of specific

With your sample data in A1:C12
and
D1: a Col_B value to find.....DG
D2: a Col_A number fo find...123

This regular formula finds the Col_C value that corresponds to the first Col_A instance of the D2 value that occurs AFTER the first Col_B instance of the D1 value
``Please Login or Register  to view this content.``
In the above example, the formula returns: 456
If you change D1 to "KL", the formula returns: 677

Is that something you can work with

3. ## Re: search for text in colB, starting with next row search for 1st occurrence of specific

try
=INDEX(C:C,MATCH("dg",B:B,0)+MATCH(123,OFFSET(B1,MATCH("dg",B:B,0),-1,10000),0))

4. ## Re: search for text in colB, starting with next row search for 1st occurrence of specific

Wow! First to Ron - That is definitely something I can work with AND...it works like a charm!
Next to Martin in UK...yours works perfectly as well.

And I thank you both for such quick responses!

Now to figure how to post this as solved!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1