Find Value in List in Cell

Art Schneiderheinze

I have two sheets---one with a list of program that includes rows with a cell that has a list of course prefixes.

EXAMPLE:

ROW 1--->Doe, John | doej@xx.com | BUS,CUL,MGT

ROW 2--->Smith, Elle | smithe@xx.com | HOS,SUS

Each row has three columns: name, e-mail address, and course code prefixes.

On the second sheet, I want to list courses to be developed in an upcoming term.

EXAMPLE:

ROW 1--->BUS 222 Contracts | 6 weeks | Doe, John

ROW 2--->SUS 318 Water Resources | 10 weeks | Smith, Ellie

Each row has three columns: course name (entered manually), course length (entered manually), and program leader (added automatically based on course code prefix. For BUS 222, I want to use "BUS" to search in the other sheet to add the correct program leader ("Doe, John"), with the corresponding prefix in the list in column 3 (BUS appears in the list with "CUL" and "MGT".

I know about the INDEX and MATCH functions, but I am not sure how to search using the LEFT three characters of the value in a cell (e.g., "BUS") in another sheet in cells with multiple prefixes, separated by commas, and put the name of the program leaders ("Doe, John").

Not every program code has 3 characters, so I am guessing I would need to find the space first in the course code ("BUS" in BUS 222 and "CAPK" in CAPK 344). Once I know this, however, I do not know how to find the one row (and the one program leader) who has that prefix in his or her list (BUS, CUL, MGT).

Thanks!

Mike Wilday

Because your list of courses is comma separated in a single column, this could get hairy. But you might consider trying to use FIND combined with an IF statement. 

https://help.smartsheet.com/function/find

https://help.smartsheet.com/function/if

Find will look through a string of text and return the starting position of the search term you indicate. So the logic would be, IF(Find("BUS", YourCrossSheetReference) > 0, "Harry")... 

If you wanted to restructure your data so the courses were in individual fields a VLOOKUP would be more appropriate. But based on your structure, you may need to use the FIND function. 

Paul Newcome

I haven't been able to make an INDEX/MATCH or a VLOOKUP work based on using a FIND. I even tried using a FIND/COLLECT to no avail.

 

Parsing out your csv's would make it much easier.

 

As it is, you are going to need a nested IF statement as Mike has suggested, but that could grow to be a HUGE pain depending on many variables you could have...

 

EDIT: I think I have found a solution for using a FIND function within an INDEX/MATCH using a JOIN(COLLECT tucked in there. It is posted above. This will only work though if that particular text you are searching for is located in ONLY ONE ROW of the column you are searching through. I will have to play some more to see if I can make it work if it is listed in multiple rows.

Art Schneiderheinze

Is there a way to look at the contents of the cell and see it contains a prefix? If it sees the prefix (e.g., BUS), it doesn't matter what else is in the cell. If the cell contains the prefix, it uses thr name in the adjacent cell.

Mike Wilday

In a sense yes, 

You could do an IF statement that uses the LEFT referece to compare the left 3 characters to whatever you want. Or in the case of a 4 character you could modify it to check the Left 4... 

IF(LEFT(ColumnReference@row, 3) = "BUS", "Do this", IF(LEFT(ColumnRefrence@row, 3)="EXP", "Do this", "Else do this"))

https://help.smartsheet.com/function/left

Does that make sense? 

Paul Newcome

That could end up being one looooooooooong IF statement. I wish there was a way to use a FIND function within an INDEX/MATCH or even a VLOOKUP.

 

I thought for sure I had done it a few weeks ago, but I couldn't find it in any of my sheets and couldn't replicate it.

Art Schneiderheinze

This was my thinking as well. If i had, say 20 rows, each with a unique program leader name in one cell and unique list of prefixes in another cell (separated with commas), i thought I could use INDEX and MATCH somehow to find the row containing a particular prefix (within the list for each program leader. Then, using that row, show the program leader's name in the other sheet.

With MATCH, I do not think you can use wildcards. So if I am looking for prefix BUS, I am not sure how to look for BUS in each list in each row to find BUS, no matter if it is the first, second, third, or only prefix.

Paul Newcome

If you were able to parse out your list to have each prefix in it's own column this would be so straightforward.

 

How many prefixes do you have? I have a few ideas, but the number of prefixes could be the deal breaker on some of them...

Art Schneiderheinze

The number of prefixes could vary from 1 to maybe as many as 6. A FIND would just look at the entire cell, and if it finds the prefix (e.g., HOS) in ANY part of the cell, it should know that's the row to find the program leader's name, right, no matter where in the cell it is:

BUS, CUL, HOS, TRT

HOS, HUM, SUS

ACC, BUS, CUL, HOS

The prefix HOS is in different spots in the three examples, but that wouldn't matter, right? The prefix would only occur in one row. I just need to have smartsheet figure out in which row it appears.

I just don't know how to write the formula if the prefix is in, say, column Prefix and program leader's name to pull from the correct row is in column Program Leader. 

The formula is in Sheet2 but the prefixes and program leader names are in Sheet1.

 

Paul Newcome

I think I may have a solution. I tested this and it worked for me. I just hope we can make it work for you *fingers crossed*...

 

=INDEX({Sheet 1 Range 1}, MATCH(JOIN(COLLECT({Sheet 1 Range 2}, {Sheet 1 Range 2}, FIND([Course Code]@row, @cell) > 0)), {Sheet 1 Range 2}, 0))

.

.

{Sheet 1 Range 1} = The name column on the first sheet you have listed in your original post

{Sheet 1 Range 2} = The column where the course code prefixes are listed separated by commas

.

.

The idea behind this came about when I re-read that the course prefix will only be listed in ONE ROW of Sheet 1. You can use a JOIN(COLLECT to say to join all cells from sheet 1 that have the Course Prefix on Sheet 2. Since it will only be listed in one row, it will only "Join" the data from one cell.

 

Since it is only one cell, you can use that to establish the Search Value for your MATCH function within the INDEX/MATCH formula.

 

In place of [Course Code]@row, since you won't always have three digits and there is additional data in the cell, as long as the prefix is the first portion of the cell, you can use 

 

=LEFT([Course Name Column]@row, FIND(" ", [Course Name Column]@row) - 1)

 

You can either put that in place of [Course Code]@row in the formula above, or you can put it in a helper column and reference that in the formula above.

Mike Wilday

Have you tried Paul's solution? 

Using FIND with an IFstatement would require you to manually write who is the contact for that row. You can use a cross-sheet reference to do the find, but you would have to code into the formula the name of the person you want there. I think that using find would be too cumbersome. 

Paul Newcome

The FIND in the IF statement would definitely get cumbersome quickly. With (what I hope is) my solution, the FIND pulls from data that is already there, so there is no additional entry required.

Art Schneiderheinze

I created a new column to parse just the prefix of the course code (which be 3 or 4 characters): Course Prefix.

Sheet 1 Range 1 ---> Last+First

Sheet 1 Range 2 ---> Prefixes

Revised your formula and placed in cell in row 42:

=INDEX({Last+First}, MATCH(JOIN(COLLECT({Prefixes}, {Prefixes}, FIND(LEFT([Subject + Course]42@row, FIND(" ", [Subject + Course]42@row) - 1), @cell) > 0)), {Prefixes}, 0))

Should search for BUS (parsed prefix) from Sheet 2 in Sheet 1 (cell in column Prefixes actually contains BUS,CUL,HOS) and pull the program leader's name from Last+First column.

No spaces after each comma in the Prefixes column, if that matters.

Get an #UNPARESEABLE error

 

 

*** NEVER MIND ***

Got it to work!!! Moved the @row in the LEFT formula:

=INDEX({Last+First}, MATCH(JOIN(COLLECT({Prefixes}, {Prefixes}, FIND(LEFT([Subject + Course]42, FIND(" ", [Subject + Course]42) - 1), @cell) > 0)), {Prefixes}, 0))

Paul Newcome

@aschneiderheinze

 

I got an email notification that you had posted an additional question, but I don't see it here. Were you able to get it figured out?

Art Schneiderheinze

Yes, Paul. I did post a reply but figured out a workaround on my own. You have been a tremendous help with your idea! Thanks for following up.

Paul Newcome

Good to hear! Happy to help!