Formula to return a value from another sheet based on multiple criterial

Kristi R

I need to pull in values to one sheet (PMT Program Filing Tracker in the screenshot) based on matching two criteria in another sheet (Program Filings in the screenshot). I’m not sure of the best formula to do this.  For example (see screenshots), in the ‘PMT Program Filing Tracker’ sheet, I need to pull in a Status from the ‘Program Filings’ sheet based on matching the “Parent Program” and “State” in the ‘Program Filings’ sheet. I’ve manually filled in the result I want the formula to return in the ‘PMT Program Filing Tracker’ Program 1 line as an example.

Thanks in advance for the help!

Darren Mullen

Kristi,

You can use Join and Collect. If you could possibly have multiple matches, then you'd need to add some additional logic to pick the one you want, but in any case, you'd likely still need to use Collect. See here for a simple example:

https://youtu.be/a0BnrRwm_Bg

Andrée Starå

Hi @Kristi R

I hope you're well and safe!

I'd recommend using an INDEX/COLLECT combination.

Try something like this.

=INDEX(COLLECT({Program Filings - Status}, {Program Filings - Parent Program}, 
[Parent Program]@row, {Program Filings - State}, "FL"), 1)

How many states/columns will you have? I'm asking because if you have a lot, we could simplify the formula using the first row to add the states.

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Darren Mullen

Kristi,

You can use Join and Collect. If you could possibly have multiple matches, then you'd need to add some additional logic to pick the one you want, but in any case, you'd likely still need to use Collect. See here for a simple example:

https://youtu.be/a0BnrRwm_Bg

Andrée Starå

Hi @Kristi R

I hope you're well and safe!

I'd recommend using an INDEX/COLLECT combination.

Try something like this.

=INDEX(COLLECT({Program Filings - Status}, {Program Filings - Parent Program}, 
[Parent Program]@row, {Program Filings - State}, "FL"), 1)

How many states/columns will you have? I'm asking because if you have a lot, we could simplify the formula using the first row to add the states.

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Kristi R

I tried both the INDEX/COLLECT & JOIN/COLLECT formulas, and both got me the result I needed. Thanks for the help, Andree & Darren! I now have two some more formulas in my tool belt to help me in the future.

Andrée Starå

@Kristi R

Excellent!

You're more than welcome!

Darren Mullen

@Andrée Starå Good suggestion with the Index collect as well. Based on taking the first row in the index, if you had duplicates you would take the first value returned by the collect function.