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!

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?