Return list of cells in Col 3 that match both values in Col1 and 2

Hi,

First of all, go to Name Manager and define a new name called A3_Absolute and in the Refers to: box type this formula: ='Base Data'!$A$3

In cell E3 of your spreadsheet, enter this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER):

=IFERROR(INDEX([Facility Name],SMALL(IF(([State]="NSW")*([Region]="R5"),ROW([State])-MIN(ROW([State]))+1),ROWS(A3_Absolute:[@State])),1),"")

Note: this will give you your desired results for State=NSW and Region=R5, though if you are wanting to get the results for other combinations also, then, rather than manually amending the relevant parts of the formula each time, it would be better to e.g. set up another table for your desired results (i.e. with a column for State and a column for Region) so that you can replace the "NSW" and "R5" with cell references.

P.S. If anyone can come up with a better way of making structured row references absolute (I had to use a defined name for cell A3 to keep it absolute in this case), please let me know. I searched around a bit and couldn't find a better solution (see links below).

This example would seem to justify my personal decision to avoid structured references in any case!

http://answers.microsoft.com/en-us/o...4-87ceb462813c

http://www.excelcampus.com/tips/abso...uctured-table/

Hope that helps

Regards

No worries! Better late than never!

Thanks for eventually getting back.

Cheers