Searching more than one worksheet to return value

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 as well as the 2024 quote log. This formula will need to have the functionality to soon add 2025 to that search requirement too.

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!

Find more posts tagged with

Formulas

collect

INDEX

Accepted answers

All comments

Michelle Choate 2

What about something like:

=(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)), "")

Then both items will be returned with a comma in between.

Mallory N

The information I am looking to pull will only be on one of the year sheets so I only need one value to appear, but I did try this formula thinking that it would still give the desired results. It gave the error #UNPARSEABLE.

I'm looking for a formula that will look at one sheet for the information, if it is not there it looks at another sheet, and in a few weeks as we roll into 2025 I'd like for it to look at a third sheet for the information to pull to this consolidated sheet.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.