IFERROR LOOK UP

Hi All

For our timesheets we use IFERROR to check another sheet against a job number, I now need this to check over multiple sheets not just 1 how can I do this please?

The current formula is below.

=IFERROR(INDEX(COLLECT({PMYS Job Tracker - Boat}, {PMYS Job Tracker - WIP}, WIP@row, {PMYS Job Tracker - Line}, Line@row), 1), "Job Not Found")

I need it to check 1 other sheet.

Thank you

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

You would replace "Job Not Found" with the second INDEX/COLLECT then wrap the whole thing in another IFERROR.

=IFERROR(IFERROR(INDEX(COLLECT({Sheet 1}, .....................), INDEX(COLLECT({Sheet 2}, .......................)), "Job Not Found")

Craig Beattie

@Paul Newcome

Thanks for your help, I am trying to do this can you see what's going wrong here please?

=IFERROR(IFERROR(INDEX(COLLECT({PMYS Job Tracker - Boat}, {PMYS Job Tracker - WIP}, WIP@row, {PMYS Job Tracker - Line}, Line@row), 1), INDEX(COLLECT({Copy of PMYS Job Tracker QC 2023 V4 Range 2}, {Copy of PMYS Job Tracker QC 2023 V4 wip}, WIP@row, {Copy of PMYS Job Tracker QC 2023 V4 line}, Line@row), 1)), "Job Not Found")

Paul Newcome

Are you getting some kind of error message? Right off, I don't see any syntax issues.

Craig Beattie

@Paul Newcome Yes getting back #INCORRECT ARGUMENT SET

Paul Newcome

That sounds like a range issue to me. Double check each of the cross sheet references to make sure that they are pointed to the correct sheet and that they are all the same size and shape.

There have been times where I will click on a column header to create a cross sheet reference, but things are running a little slow. Then the sheet fully loads in the creation window and the selection automatically reverts back to the "home cell" (top left corner of the sheet), and I have to reselect the column.

Craig Beattie

@Andrée Starå Sorry to bother you again, have you any idea why this formula is not working. I cant seem to work it out

Paul Newcome

Did you double check your ranges?

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc--incorrect-argument-set

Craig Beattie

@Paul Newcome Yes I went through them couldn't see any issues

Paul Newcome

I don't see any syntax issues, so there are only two other things I can think of that would cause that error.

1) Double check the data within all referenced cells to see if that error is present.

2) Internal bug. Sometimes a bit of a bug gets stuck on the back end. It can usually get cleared up by "turning it off and then on again".

Remove the formula completely.

Sign out.

Clear your browser's cookies and cache.

Sign back in.

Retype the formula (not paste).

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.