Use IF/AND to find 2 values (TODAY's date and another cell), then return a different cell's value

Ami Veltrie

How can I search a 'range' to return the value from Row 23 "Goal Date (PS)" (11/4/21) using an IF/AND based on Created date (TODAY) and Pod # (Pod 3)?

Ami Veltrie

@Brett Wyrick

I figured it out! Thank you for your help.

=INDEX(COLLECT([Goal Date (PS)]:[Goal Date (PS)], Created:Created, TODAY(), [Pod #]:[Pod #], "Pod 3"), 1)

Brett Wyrick

Within your formula, instead of "[Goal Date (PS)]:[Goal Date (PS)]", replace that with "[Goal Date (PS)]@row" if you're wanting the value from the row, otherwise replace it with "[Goal Date (PS)]23" if you specifically want the value from row 23. Does that work?

Brett Wyrick

Ami Veltrie

@Brett Wyrick Hi Brett! Thanks for speedy response.

If I use "@row" it wants to return the "Goal Date (PS)" value from Row 13, not Row 23.

No, I don't want to specifically code in Row 23 because the Row number will be different the next day. I want it to scan all Rows for Today's date and "Pod 3".

Is there a way to use the range?

Brett Wyrick

I think I know what you're going for, but I'm a bit unsure still.

You'd probably want to utilize Index/Match functions.

Tell me if this formula gets what you're going for:

=INDEX([Goal Date (PS)]:[Goal Date (PS)], MATCH(LEFT([Primary Column]@row, 5), [Pod #]:[Pod #]))

Here's how INDEX/MATCH works, in a nutshell:

=INDEX([Range of data to be displayed from], MATCH([Identifier], [Range to look for Identifier], [sort option]), [optional column index])

Ami Veltrie

@Brett Wyrick

Does INDEX/MATCH work with two variables (Created Date and Pod #)?

I don't see "= TODAY()" as part of your formula.

Ami Veltrie

@Brett Wyrick

I figured it out! Thank you for your help.

=INDEX(COLLECT([Goal Date (PS)]:[Goal Date (PS)], Created:Created, TODAY(), [Pod #]:[Pod #], "Pod 3"), 1)