Search multiple sheets to return data to consolidated sheet

Mallory N

I am utilizing multiple worksheets (one for each year) but have one sheet that has some consolidated information, and I would like for it to be able to pull from the appropriate year sheet.

=IFERROR(INDEX(COLLECT({Quote Log 2024 Quote Status}, {Quote Log 2024 Quote #}, @cell = [Quote Number]@row), 1), "")

I would like for the formula to pull from the 2023 quote log to return a quote status, if the quote information is not on the 2023 log I would like it to search for the information on the 2024 log, also if not on either of those sheets I'd like for it to search the 2025 log.

I've tried this too, there is no error message, but it is not returning anything.

=IFERROR(INDEX({Quote Log 2024 Quote #}, {Quote Log 2024 Quote Status}, @cell = [Quote Number]@row), IFERROR(INDEX({Quote Log 2023 Quote #}, {Quote Log 2023 Quote Status}, @cell = [Quote Number]@row), ""))

Any help is greatly appreciated!

Paul Newcome

The second formula is almost there. You have the right idea but forgot your COLLECT pieces in each of the INDEX functions.

You went from

INDEX(COLLECT(…………), 1)

to

INDEX(…………….)