Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

index match and large

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

INDEX, MATCH and LARGE formula

ResolvedVersion 2003

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.

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

Excel tip:

Charts

Select the data and press the function key F11 and the chart will be created on a separate worksheet.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.