How would I get my VLOOKUP to return multiple Values?

Jerell Parker

Is there a way to get my VLOOKUP to return multiple values?

Current Formula:

=IF(ISBLANK([(REC) - Project Number]@row), "Part Number", IFERROR(VLOOKUP([(REC) - Project Number]@row, {QC1}, 3, false), "Part Number"))

In my example image, there are 5 Project Numbers with unique Dates / Part Numbers / Part Descriptions / PO Numbers is there a way to get my VLOOKUP to pull the Unique Values despite the Project Numbers being the same? I attempted to change false to true, but that only read the first value from bottom to top, please advise, thank you!

Paul Newcome

You may want to look into an INDEX/COLLECT.

=INDEX(COLLECT({Column To Pull From}, {1st Criteria Range}, 1st Criteria, {2nd Criteria Range}, 2nd Criteria, ........................), 1)

Jerell Parker

Hey, @Paul Newcome thank you for the feedback! I will attempt the formula you listed, but I see you have 1st Criteria, 2nd Criteria, etc... Does this mean that the values have to be known in order to set them up in the formula?

Paul Newcome

I'm not sure I understand what you are getting at here. What exactly are you trying to pull?

Jerell Parker

Hey @Paul Newcome, so I have a data sheet that lists Quality Control Metrics Per Project Number (Some of these numbers are duplicates).

In another sheet, I want Project Managers to be able to plop in their Project Numbers in the respective column and for the VLOOKUP to collect all the metrics listed in the QC Metrics Datasheet related to those jobs.

Baring that in mind, the question I above was does Criteria 1 & Criteria 2 have to be known for the formula you provided to run. In this case, I am assuming the Project Numbers would be the criteria. The only issue is, Project Numbers are not known until I am provided them by Project Managers so it would be difficult to set the VLOOKUP criteria range as this value is a dynamic variable.

Does that help clarify my question?

I have sometime later today to play with the formula, hopefully, what your provided works and I am just overthinking, haha.

Paul Newcome

How would you want it displayed? Would you want multiple matches on Project Number 12345 to be in the same cell with some sort of delimiter?

Are you planning on doing multiple different formulas to pull from different columns into multiple columns?

Are you able to provide a screenshot with data manually entered that shows the desired outcome?

Jerell Parker

Hey @Paul Newcome , please see the attached image.

On the left-hand side is the QC Inspection Log Sheet/Data Sheet. On the right-hand side is the PM Project Number Drop Sheet. Notice how in the QC Inspection Log Project Number (123456) the Date / Part-Number / Part Description / PO Number, are all different, however, in the PM datasheet only the first Project Number's (123456) is Date / Part-Number / Part Description / PO Number is being pulled by the VLOOKUP.

Ideally, PMs would plop their Project Numbers in, and the remaining columns to the right of the Project Number column ( which match the QC Data Sheet) will be auto-populated via the VLOOKUP. The desired outcome is for the VLOOKUP to pick-up the unique values even if the Project Numbers are the same.

Paul Newcome

My question then would be...

If you have multiples of the same project number, how do you determine exactly which (for example) part number to pull?

Jerell Parker

@Paul Newcome , that is the million-dollar question, that I'm trying to answer, haha. I've attempted to change my formula from false to true, but that only starts the VLOOKUP from bottom to top which doesn't resolve the issue.

=IF(ISBLANK([(REC) - Project Number]@row), "Part Number", IFERROR(VLOOKUP([(REC) - Project Number]@row, {QC1}, 3, false), "Part Number"))

Paul Newcome

You have to have something unique on each row to reference to be able to pull a different line. Even if you use a column to replicate row numbers and reference those. If you have a column that replicates the row number on the source sheet, Then you could use the INDEX/COLLECT and include the range/criteria of the row number being greater than the last row number pulled for that particular project number.

What is the purpose for pulling all of this data into another sheet?

Jerell Parker

Hey @Paul Newcome, thank you for all your help trying to resolve this issue. I ended up meeting with Smartsheet Pro-Desk to verify if we were crazy, or were just trying to do something that could not be done in Smartsheet. During the meeting, we concluded that what I was trying to do is something that cannot be done in Smartsheet using formulas at the moment, but a report will suffice for the time being.

Thanks again, and have a great weekend!

Cinnaban

Hey is there an update to this? I've run into the same problem and hoping that since this topic is a couple years old there's a newer solution.

Paul Newcome

@Cinnaban Are you able to provide more details?

Cinnaban

Please read through the original thread.

Like the previous user - VLOOKUP is returning #NOMATCH for values that may have multiple rows associated with them.

Table 1: Column1 Labels

Table 2: Column1 Labels, Multiple Products

VLOOKUP(Table1 Labels, Table 2, 2) -> should return "Multiple Products"

Works: Table 1 single Column1 Labels = Table 2 single Column1 Labels AND single Multiple Products

Does Not Work: Table 1 single Column1 Labels = Table 2 single Column1 Labels AND (Greater than 1) Multiple Products

End result is hoping to build a column that ties "Multiple Products 1", "Multiple Products 2", etc in a list format within a single column.

Paul Newcome

@Cinnaban It is an old thread, and I try not to assume that two users have the same exact situation. Are you able to provide screenshots (with sample data if needed) for reference?

LakeWaconia

Paul - I have the same issue. I have a file "Part 3" that lists "Stock Code". This stock code may appear multiple times with a different value in "Raw P/N". I'd like to pull into the sheet "Weldment BOM" into the "Weldment Number" colum every "Raw P/N" that occurs with a "Stock Code" that meets the criteria. Index(Distinct(Collect looks like my best bet, but can't get it to work.

=INDEX(DISTINCT(COLLECT({Part 3 Raw P/N}, {Part 3 Parent P/N}, [Stock Code PMI #]@row)), Helper@row) returns #Invalid Value