98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » INDEX, MATCH and LARGE formula
INDEX, MATCH and LARGE formula
Resolved · Urgent Priority · Version 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
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Convert Text to Columns in Excel 2010If you have a cell in your Excel spreadsheet that contains a lot of text and you want to divide it into separate columns, this can only be done if there is a logical character which separates the text, for example, a comma. |