Solved

Match Formula?

Posted on 2011-02-11
8
190 Views
Last Modified: 2012-05-11
Hi,

I have a row of data in column B, there may be data anywhere from B1-B10,000. Can i return to M1, M2, M3 etc any results.

The data in ColB will always be "Double Div"

So if theres an instance return it, i can guarantee this wont happen more than 50 times so i could drag down till then.

Ive attached the file

Thanks
Seamus

CP-Report.zip
0
Comment
Question by:Seamus2626
  • 4
  • 2
  • 2
8 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 34871608
Seamus - do you mean col A? Col B is empty. An instance of what - not clear what you are looking for?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34871614
What exactly do you want returned?
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 34871641
OK, I think I see now. You want B37, B195 returned in M1, M2 etc?
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 250 total points
ID: 34871670
My best guess - in M7:
=IF(COUNTIF(INDEX(B:B,C7):INDEX(B:B,IF(C8="",65536,C8-1)),"Double Div")>0,"Double Div","")
and copy down.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Seamus2626
ID: 34871704
Sorry guys, i have to shoot, i will return first thing Mon Morning.

Thanks for the help

Seamus
0
 
LVL 24

Assisted Solution

by:StephenJR
StephenJR earned 250 total points
ID: 34871867
Perhaps this in M1 and down. An array formula so Ctrl+Shift+Enter:

=IF(ROWS(M$6:M6)<=COUNTIF($B$1:$B$1500,"Double Div"),"B" & SMALL(IF($B$1:$B$1500="Double Div",ROW($M$1:$M$1500)-ROW($M$1)+1),ROWS(M$6:M6)),"")
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 34871877
Should be, in M6 and down.
0
 

Author Closing Comment

by:Seamus2626
ID: 34886665
Thanks lads, both worked perfectly!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now