Bringing back specific information from a larger data set

[mr_vic]

All,

I want to take specific wage location data from a data set spreadsheet and place this lump of data onto a data retrieved tab.

I think that a type of index / match will do this but i don't have the knowledge to create the formula.

I have enclosed a spreadsheet including narrative of what i'd like to achieive.

Thanks for your help

JB

[AlKey]

Try this formula in C5 (See attached file)

=INDEX('Data Set'!C:C,SMALL(IF('Data Set'!$A$5:$A$22=$A5,ROW('Data Set'!$A$5:$A$22)),ROWS($C$4:C4)))

Pull formula across and down

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[mr_vic]

AlKey,

Firstly thank you so much for your response, I forgot to add a cell into the previous update on the spreadsheet on the retrieved data tab. I have now added this into cell C4.

In would like the index and match to be driven from the C4 cell and for this to bring back all of the information for location 3 (as per my example).

If i were to change my location to location 5 on my front sheet, i would then like the formula to bring back all of the location 5 information from the data set tab and put this onto the retrieved tab.

I have made the relevant changes to the spreadsheet and added additional narrative.

Thank you for all of your help. Once this is solved i'll add to your reputation.

JB

[AlKey]

Please see attached file with revised formula.

[AlKey]

Thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).