CONTAINS formula

NateP

Trying to get a formula to lookup a reference sheet to see if the ID contains the word BASE in it and return with a value SKUBASE if so and SKU if not. I feel like I'm close but just missing something.

I had a formula where if I checked the box "add base" it would simply add the suffix BASE to the id however this is all manual. The reference sheet (second screenshot) is a direct export from my source and I want to make sure I don't miss any values that might contain BASE.

I wrote the formula: =IF(CONTAINS("BASE", {3dcart import Range 1}), SKU@row + "BASE", SKU@row)

but this just seems to add the suffix BASE to every row because its not looking at the id in the row, it's just looking to see if BASE is anywhere in the column and adding it to every row SKU. Seems like I need a VLOOKUP but I know those only return exact values so the BASE suffix would eliminate it.

NateP

Thanks James and Kelly. Before I got James' suggestion, I had basically done what he had suggested. I created a couple helper columns in the reference sheet, one which stripped BASE out of the id and one which identified if that row had BASE included.

The formula I used to strip the word BASE out was =IF(BASE@row = 1, LEFT(id@row, (FIND("BASE", id@row) - 1)), id@row)

I think I had a formula in the BASE column which resulted in either just a 1 or 0 but once I got that value, I might have just converted them to basic 1s and 0s instead of a formula. I can't remember now.

Then in the main sheet, I used the following formula: =IFERROR(IF(VLOOKUP(SKU@row, {external reference}, 2, false) > 0, SKU@row + "BASE", SKU@row), SKU@row)

This got me my end goal of looking at the SKU row in the main sheet, and if in the reference sheet that SKU has a BASE suffix, adding that suffix to the SKU in the id row of the main sheet.

https://community.smartsheet.com/discussion/comment/323072#Comment_323072

Kelly, I tried the INDEX formula you suggested and it didn't seem to give me the results I was looking for. I haven't really used INDEX formulas before so I didn't know how to tweak it to achieve what I needed. Regardless, a little more time on the community pages and I saw some almost completely unrelated post that gave me the idea for the helper column that stripped the suffix out. Thanks for your help. I'll try playing with the INDEX and COLLECT formulas to see if I can get those results.

NateP

In case its not clear, i don't want to use the checkbox anymore

Kelly Moore

Hey @NateP

I believe you are asking to use the SKU in your top screenshot to find the ID in your bottom (Reference sheet) screenshot. This formula will be located in the top screenshot ID column

=INDEX(COLLECT({Reference Sheet ID}, {Reference Sheet ID}, CONTAINS(SKU@row, @cell)), 1)

Remember when using cross sheet references you must physically create each reference - you cannot simply copy paste the formula in your sheet.

Does this work for you?

Kelly

NateP

https://community.smartsheet.com/discussion/comment/322671#Comment_322671

Not quite. What I'm looking for is to see if the SKU in the main sheet, is referenced in the the reference sheet with the suffix BASE and if so, add that suffix in the id column of main sheet. Here's some more examples. You can see that not all SKUs can find a reference with the BASE suffix so they don't get the suffix in the id column, but those that do find it, get the suffix. So searching for BASE and including it is definitely needed in the formula.

James Keuning

There is a more efficient way to do this, but these steps will get you there.

Create a helper column in the main table that strips the BASE out of the id field. Then in table 2 use match to look for the id in the main table, when match gives you the row, use index to pull the value, then you compare those. You can even use index to pull the word BASE over and concatenate that to your ID.

If you post your table names, field names, and ten rows (text, not screen shots) from each table I can explain it better.

Kelly Moore

Hey @NateP

As you try the different approaches, did you try my method? I believe you will find it does what you describe.

NateP

Thanks James and Kelly. Before I got James' suggestion, I had basically done what he had suggested. I created a couple helper columns in the reference sheet, one which stripped BASE out of the id and one which identified if that row had BASE included.

The formula I used to strip the word BASE out was =IF(BASE@row = 1, LEFT(id@row, (FIND("BASE", id@row) - 1)), id@row)

I think I had a formula in the BASE column which resulted in either just a 1 or 0 but once I got that value, I might have just converted them to basic 1s and 0s instead of a formula. I can't remember now.

Then in the main sheet, I used the following formula: =IFERROR(IF(VLOOKUP(SKU@row, {external reference}, 2, false) > 0, SKU@row + "BASE", SKU@row), SKU@row)

This got me my end goal of looking at the SKU row in the main sheet, and if in the reference sheet that SKU has a BASE suffix, adding that suffix to the SKU in the id row of the main sheet.

https://community.smartsheet.com/discussion/comment/323072#Comment_323072

Kelly, I tried the INDEX formula you suggested and it didn't seem to give me the results I was looking for. I haven't really used INDEX formulas before so I didn't know how to tweak it to achieve what I needed. Regardless, a little more time on the community pages and I saw some almost completely unrelated post that gave me the idea for the helper column that stripped the suffix out. Thanks for your help. I'll try playing with the INDEX and COLLECT formulas to see if I can get those results.

Kelly Moore

Glad you got it working. The intent with the index/collect is a lookup. You shouldn’t have to recreate an answer that is already existing in your reference table. You should be able to bring the answer directly over.