Workaround for VLOOPUP with multiple search values

wei shen

Hi all,

I would like to populate column(serial number) on sheet 1 based on information on sheet 2.

If sheet 2 column(product type) contains "A","B","C","D" take the corresponding sheet 2 column(serial number) .

This is my formula which I've tried and returns UNPARSEABLE.

IF(OR(CONTAINS("A", {sheet 2 product type}),CONTAINS("B",{sheet 2 product type}), ...), {sheet 2 serial number}@row)

Appreciate any help rendered. Thanks in advance!

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Are you able to provide a screenshot with sensitive/confidential data removed, blocked, and/or replaced with mock data?

wei shen

Hi Paul,

Thanks for your reply.

Sheet 2 will be empty in the beginning

Serial number for both matching chasis and engine are the same.

Engine workstation -1 only builds Engines for Car models A,B and C.

First look for either model A, B or C in column I, if found take the corresponding serial number in row J and populate column M

I did a quick mock up on Excel, I hope this helps you visualize things a little better.

smartsheet forum.JPG

Paul Newcome

What is the purpose of sheet 2?

wei shen

@Paul Newcome

Sheet 2 tracks the build progress of the engine in that particular workstation. The workstation only produces 3 out of 5 available engine types.

Sheet 1 can be looked at as the main project sheet with all the production orders for cars. The engine workstation needs to look through Sheet 1 and determine which order requires them to build engines for.

Paul Newcome

Does sheet 2 get updated with anything or do you have metrics running off of it, or is it simply just a "filtered" view of the main sheet?

wei shen

The common data (customer name, commit date,etc) is filtered and referenced through the criteria above. The sheet is then updated daily with a gantt chart to check the build progress.

Paul Newcome

Have you looked into Reports?

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.