Bring back rents per location from data table

All,

Hope you are well. I want to create a basic report which allows me to pull rent numbers per several locations (locations A to F) onto a new tab.

I have enclosed an attached spreadhseet of what I am trying to achieve. A description of each tab is below:

Front sheet tab: This tab will allow me to select the specific location from location A to F
Data set tab: This shows each location, number of units within each location, the name of the unit and the rent per month for that unit
Retrieved data tab: This is the output tab of that I would like to see extracted from the data set tab for location A.

I don't want to pull this data together in a pivot table becaue i will use the front sheet to pull through other data in the future.

I hope this makes sense. Please get in touch if you require further clarification.

Cheers,
Mr_V

Originally Posted by :) Sixthsense :)

In A6 Cell of Retrieved data Sheet - Array Formula - Requires CTRL+SHIFT+ENTER

=IFERROR(INDEX('Data Set'!$A$5:$D$32,SMALL(IF('Data Set'!$A$5:$A$32=$C$3,ROW('Data Set'!$A$5:$A$32)-MIN(ROW('Data Set'!$A$5:$A$32))+1),ROW($A1)),COLUMN(A$1)),"")

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Drag it down and right...

Hello Mr Sixthsense,

Thank you for the reply. I've copied this formula into the retrieved dataset as instructed and it is only bringing back data in the 1 row.

I have enclosed a copy of the updated attachment. Where am i going wrong?

Thanks once again for your help.
Mr_V

formula at A6 sheet retrieve data (array)
=IFERROR(INDEX('Data Set'!A$5:A$32,SMALL(IF('Data Set'!$A$5:$A$32='Retrieved data'!$C$3,MATCH('Data Set'!$C$5:$C$32,'Data Set'!$C$5:$C$32,0)),ROW(1:1))),"")

copy right and down

Guys,

I'm sorry i've now copied this formula into the attached spreadsheet and used the array formula calcuate and it is still not working.

Can i be cheeky and ask for you to tet the formula within the template.

Sorry to be a pain.

Mr_v

see this attached

Thanks gents! This is amazing.

Rep will be added.