Formula to Lookup Multiple Matches

I have a "Excel Table" defining Units for properties.

UnitID Unit Name Property Building Floor Number Square Feet
1 101 111 South St 1 1 1,200
2 102 111 South St 1 2 1,500
3 101 678 W Gore Rd 1 1 1,253
4 101 112 South St 1 1 3,250

I have another table defining Tenants for the units.

PersonID Property Unit First Name Last Name
1 111 South St 101 Max Rentit
2 112 South St 101 Daren Renterson
3 112 South St 101 Karen Leaseitgood


On the tenant "Property" is a drop down defined by another sheet... I select the property, and that selection will help me filter the unit. The Problem is, my formula will only return ONE unit, and not both for each property.

I am not a total noob with Excel, but I am with this functionality.

Here is my Formula


=INDEX(Unit!$B:$B, MATCH(Tenant!B2, Unit!$C:$C, 0))

Explained: Look in Unit Table column B (Property Address), Match Unit Table Column B With Tenant Table Column B and Return COLUMN C for and matched column b value.

Currently if I select Property "111 South St" the formula only returns 101 and not 101 and 102 for a drop down.

I went to the best forum I could find on Excel for this question. I am hopeful!

1) Create a named range called Properties and give it this RefersTo:
=OFFSET(Unit!$C$2,,,COUNTA(Unit!$C:$C)-1,)

This is a dynamic range that will expand itself based on the number of rows you fill out on the Unit sheet.

Be sure to keep this page sorted by column C, all the like addresses need to be together.

2) Use this data validation setting in C2 on Tenant:

Allow: List
Source: =OFFSET(Properties,,-1,COUNTIF(Properties, $B2),)


3) Apply Conditional Formatting to C2 so that if you select an option in C2 from the drop down, then go back to B2 and change the address and the choice in C2 no longer matches the list available in the C2 drop down, this CF will turn the cell black to remind the user to fix C2 as well:

=AND($C2<>"", NOT(ISNUMBER(MATCH($C2, OFFSET(Properties,,-1,COUNTIF(Properties, $B2),), 0))))

I have marked this thread solved for you.
In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.