I am using SharePoint online and am having trouble with the formulas since they are not exactly like Excel or InfoPath.

This custom list I am creating has a lookup column for office locations and I want the field to filter only the offices in the same state as the team member. Can someone help me with this, details below. I hope I explained this right.

Lookup List (Office Locations)

• Columns in list
  1. LocationID
  2. State

List (Custom List)

• Columns in list
  1. Preferred Location
  2. State