Need formula that will look up a cell in another column, if true return "Complete", if not "Waiting"

This is the formula that works in excel - I cannot replicate in Smartsheet

Essential what I need is If the item in column A is in Column B return "Complete" if not return "Waiting"

'=IF(IFNA((VLOOKUP(A2,'[Win 11 Upgraded Assets.xlsx]CDN All Windows 11 23H2 Workst'!A$1:A$3700,1,FALSE))," ")=" ","Waiting","Complete")

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

You want it to return "Complete" if it is found regardless of what you have in the other column, or do you want it to pull whatever value is in the other column an bring it over?

Courtney S.

It looks like you've set up your IF statement to check if the value was NOT found and return Waiting, and then the "if else"/"Otherwise"is to return Complete. What if you did it the other way around in Smartsheet? If the VLOOKUP value = the searched for value, then Complete, otherwise Waiting.
Something like: IF(Name@row =VLOOKUP(Name@row, {cross-sheet reference of the column containing all completed assets}, 1, false),"Complete","Waiting")

Tanya B

This is what I get

Paul Newcome

@Tanya B Remove the closing square bracket after Upddated@row. You should also have a range instead of a cell reference there in the second portion of the VLOOKUP.

But if all you are doing is checking to see if it is found, a COUNTIFS will be more efficient at large scale.

=IF(COUNTIFS(Upddated:Upddated, @cell = [Asset#]@row) > 0, "Complete", "Waiting")

Tanya B

That worked! Thank you!

Paul Newcome

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.