Formula

Susan van Niekerk

Hi Brain trust

Need help with a formula - should be an easy xlookup :-/

I want to return the Planned Date when my Index match Helper Column matches the Heritage Field trip column on my current sheet, and if no match is found return "Not Found"

have tried both below and just keep getting errors

=INDEX([Heritage Field Trip]@row, MATCH({Index Match Helper}, {Planned Date}, 0))

=IFERROR(INDEX([Heritage Field Trip]@row, MATCH ({Index Match Helper}, {Planned start date}, 0)), "Not Found")

Paul Newcome

Are you able to provide screenshots for context?

Your MATCH functions are off. The first piece should be either a "text string" or some other data or a cell reference to something on the same sheet as the formula, not a {Cross Sheet Reference}.

Similar (but reversed) idea for the first range in your INDEX function. Generally that is going to be a {Cross Sheet Reference} pointing to the same sheet as the second piece in your MATCH function.

=INDEX({Planned Date}, MATCH([Heritage Field Trip]@row, {Index Match Helper}, 0))

Paul Newcome

Are you able to provide screenshots for context?

Your MATCH functions are off. The first piece should be either a "text string" or some other data or a cell reference to something on the same sheet as the formula, not a {Cross Sheet Reference}.

Similar (but reversed) idea for the first range in your INDEX function. Generally that is going to be a {Cross Sheet Reference} pointing to the same sheet as the second piece in your MATCH function.

=INDEX({Planned Date}, MATCH([Heritage Field Trip]@row, {Index Match Helper}, 0))

Susan van Niekerk

worked a treat thanks Paul

Paul Newcome

Happy to help. 👍️