VLOOKUP with concatenate

Megan B.

My project has a "master project" sheet and then dozens of individual project sheets.

On the mast project spreadsheet, people will enter 3 letter customer mnemonics [Organization] and select from a drop down list in [Project}. The drop down list could be a single or multiple from that list.

The primary column is a combination of two fields from the project spreadsheet =(Organization@row + " "+ Project@row).

On an individual project sheet, I need to do a VLOOKUP to see if there is a different project for that customer (where some of the data could be duplicated for this new project sheet). My VLOOKUP is

=VLOOKUP(Organization@row + " " + "other project text string", {Master Project Sheet Range 3}, 1, false)

I'm not returning any matches unless I ctrl+c, ctrl+v the primary key and hand feed that into the VLOOKUP. I suspect the problem is the dropdown data element in the list. In the cell, it looks like there is a carriage return and when I copy paste that into TextPad and reveal the chars, that appears to be the case as well. (between MCB and CAM)

Here's how I am combining those two fields currently:

Is there some sort of data conversion I need to do on the "Project Name" field (also the primary column) to it will match on a VOOLUP with test inputs (e.g. the VLOOKUP on the project sheet is not a concatenation of two fields on that page.

Thanks!

Jason Tarpinian

I think you are ready to step up to INDEX(COLLECT()), once you learn this you will never want VLOOKUP again 😁

If I'm understanding correctly, in short, you want to return a value based on multiple criteria? The COLLECT() function works perfectly for that. In the example below, the COLLECT() function is filtering out the "Value to Return" data based on 2 criteria: that the Organization = my Organization Lookup field, and the Project = my Project Lookup field, and then returning the 1st match (ideally when you use COLLECT like this, there'll be one match only, but it's useful for multiple matches in certain cases.

=INDEX(COLLECT([Value to Return]:[Value to Return], Organization:Organization, [Organization Lookup]@row, Project:Project, [Project Lookup]@row), 1)