INDEX, MATCH and LARGE formula

Resolved · Urgent Priority · Version 2003

Thu 22 Sep 2011, 07:50 Delegate 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.

Fri 23 Sep 2011, 15:17 Trainer 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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Convert Text to Columns in Excel 2010

If 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.

Select the cells you would like to convert. On the Data tab, click Text to Columns. Choose the format of your current data.

Select Delimited if the text contains a logical character otherwise select Fixed Width if there are a certain number of spaces between each field.

Click Next when a preview of the data appears. Then select the type of character that separates the various fields. If the character is not listed, select Other and enter the character.

Click Next again and then choose the format for each of the columns. Select the column heading in the Data preview and then select a data type from the Column data format options.

Click Finish and the text will appear in several columns.

View all Excel hints and tips