How to pull data for a column from another sheet based on criteria from two columns?

MACChelsea

I currently have two Smartsheets. One Smartsheet is a contact list based on location and department. The other sheet needs to be able to pull a contact from the contact list based on the location and department selected. The formula I tried that is not working is below.

=INDEX(COLLECT({CI Board Contact List Facilitator}, {CI Board Contact List Location}, Location@row, {CI Board Contact List HEAVY Department}, Department@row), 1)

Currently, this other formula , below , works for just one of the criteria (location), but I need something that will work for two criteria (location and department).

=INDEX({CI Board Contact List Facilitator}, MATCH(Location@row, {CI Board Contact List Location}, 0))

TVang

There may be a more efficient approach, but one approach is to have a helper column, LocationAndDept, in each sheet, and set it to =Location@row + Department@row. Perform your MATCH() on that column.