index match and large
The UK's Number 1 for Microsoft Office Training Add this page to your favourites/bookmarksBookmark page
 
View printable version of pagePrintable version
Plus One Google
Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » INDEX, MATCH and LARGE formula

INDEX, MATCH and LARGE formula

· Urgent Priority · Version 2003

replyReply Thu 22 Sep 2011, 07:50Delegate Gareth said...

Gareth has attended:
No courses

INDEX, MATCH and LARGE formula

Hello

I am having difficulty with the Index and match function.

I have a spreadsheet where I have built a userform and the data from the user form is sent to a 'Data Store' tab.

From the 'Decision Overview' tab I would like to summarise the data that is sent to the ' Data Store' tab. The relevent data in that tab is the decision number in column A and the Policy Detail in column K. The overview tab will just have the name of the policy detail such as 'Investigation Objectives'.

As data is sent to the data store a number of decision numbers will be related to the detail 'Investigation Objectives'. For example, decision numbers 1,5,17,35 etc will be tagged as 'Investigation Objectives' in the data store.

I would like a formula that will look down column K and match 'Investigation objectives' (preferably by pointing to a cell on the overview sheet not by the text "Investigation objectives"), it will then go down column A and find the decision numbers. But I would like the numbers to be in ascending order so it will have to to find the largest / smallest and work back from there.

The data in the data store is unsorted but I can build a macro into the userform to sort this if needed.

I have used the Index and match function (B9 is the cell reference for "Investigation objectives")

=INDEX('Data Store'!$A:$A,MATCH($B9,'Data Store'!$K:$K,0))

and it works but I need to retrieve a range of numbers that match B9 and not just the one. I think the large or small function might be needed but I cant seem to get it to work.

Any help much appreciated. I have tagged this as urgent as I have a deadline to meet.

Thanks in advance

Gareth.

For upcoming training course dates see: Pricing & availability

replyReply Fri 23 Sep 2011, 15:17Trainer Jacob said...

RE: INDEX, MATCH and LARGE formula

Hi Gareth

Thanks for your forum post. We have had a look at the detail you have provided and there are no quick answers I'm afraid.

This sort of post takes us out of the scope of the forum as we would really need to see your actual working files to try and figure out the difficulties you are facing.

I appreciate you are under a deadline and therefore we might not be able to assist, but the next step would be for you to email us your file for review. We would then get back to you (response time dependant on who in the team is available and not training) to let you know if there is a solution, how much development time it will take and any associated trainer development costs.

I will drop you an email in case you wish to look into this further.

Kind regards

Jacob

 

Please browse our web site to find out more about
microsoft excel training london and other Microsoft training courses.

Excel tip:

Random Numbers

Type =RAND()*200 to generate a number between 1 and 200.
Use the fill handle to drag down and populate as many cells as you'd like with random numbers.

View all Excel hints and tips


Microsoft Certified Partner Accredited Training Provider: Institute of IT Training Institute of Leadership and Management - Certified Courses Security Seal verified by visa, mastercard securecard