IF FIND- help with formula

[Katy_Richards]

Hello. I have a lookup field which lists the age groups that are related to all the linked records. I have a formula field which I’d like to show ‘Senior’ if the lookup field contains Senior, ‘Intermediate’ if it contains Intermediate (but not Senior) and ‘Junior’ if it only contains Junior. The formula I’m using is this:

IF( FIND( ‘Senior’, {Lookup Correct Section} ), ‘Senior’, IF( FIND( ‘Intermediate’, {Lookup Correct Section} ), ‘Intermediate’, IF( FIND( ‘Junior’, {Lookup Correct Section} ), ‘Junior’ ) ) )

If it is listed “Intermediate, Senior” then the formula works perfectly however if it is “Senior, Intermediate” then it returns Intermediate (even though Senior is in there).

I’m sure it’s something really simple that I’m missing, I’d really appreciate any help!

Katy

[Rupert_Hoffsch1]

Hi Katy, I think you have to explain again what exactly you’re trying to achieve. Why are you using that formula if the lookup field already shows you the age groups? :v:

[ScottWorld · Accepted solution]

@Katy_Richards Your formula might not be working because the field {Lookup Correct Section} is an array, not a string of text.

Everywhere in your formula where you’ve used {Lookup Correct Section}, try changing that to:

 {Lookup Correct Section} & ""

[Shota_Kondo]

Hi guys, I was about to post a similar question where i saw @Katy_Richards question.
I believe the answer for Katy’s question will also answer mine.

IF(
	FIND(‘Ueki',  {Order Tags} & ‘’), 
	‘Ueki’, 
   	IF(
      		FIND(‘Ogawa',  {Order Tags} & ‘’), 
    		‘Ogawa’,
    		IF(
	      		FIND(‘Suzuki',  {Order Tags} & ‘’), 
    			‘Suzuki’,
    			IF(
      				FIND(‘Morita',  {Order Tags} & ‘’), 
	    			‘Morita’,
				IF(
      					FIND(‘Kondo',  {Order Tags} & ‘’), 
    					‘Kondo’,
					IF(
 	     					FIND('Inoue',  {Order Tags} & ‘’), 
   						‘Inoue’,
						'No Support'
					)
				)
			)
		)
   	)
)

Our {Order Tags} will include results such as “direct, SYSTEM, Suzuki, ShippingOK.”
Essentially, I have Shopify’s order tags being sent over to Airtable, and would like to Make a formula column that is designated to the person’s name providing support for the order.

If there’s anything I can be of assistance with in this thread, please let me know :slightly_smiling_face:
Thanks guys!

[Justin_Barrett]

This kind of searching can be simplified using a regular expression:

IF(
    REGEX_MATCH({Order Tags} & "", "Ueki|Ogawa|Suzuki|Kondo|Inoue"),
    REGEX_EXTRACT({Order Tags} & "", "Ueki|Ogawa|Suzuki|Kondo|Inoue"),
    "No Support"
)

[Justin_Barrett]

To address @Katy_Richards’ situation, the formula would look like this:

IF(
    REGEX_MATCH({Lookup Correct Section} & "", "Senior|Intermediate|Junior"),
    REGEX_EXTRACT({Lookup Correct Section} & "", "Senior|Intermediate|Junior")
)

[kuovonne]

Justin_Barrett:

To address @Katy_Richards’ situation

I don’t think that this will address the situation. If the field contains “Senior, Intermediate” or “Intermediate, Senior”, she wants the formula to return only “Senior”.

I believe that Scott’s answer is correct in this case (if the lookup field remains a lookup field):

IF( 
    FIND( 'Senior', {Lookup Correct Section} & ""), 
    'Senior', 
 IF( 
    FIND( 'Intermediate', {Lookup Correct Section}  & ""), 
    'Intermediate', 
IF( 
    FIND( 'Junior', {Lookup Correct Section}  & ""), 
    'Junior' 
) ) )

On the other hand, I find that lookup fields are the most difficult fields to use in formulas, as the resulting type is not consistent. Instead, I recommend converting the lookup to a rollup, in which the original formula should work.

[Justin_Barrett]

kuovonne:

I don’t think that this will address the situation. If the field contains “Senior, Intermediate” or “Intermediate, Senior”, she wants the formula to return only “Senior”.

Sorry, I skimmed the original a bit too quickly.

[Katy_Richards]

Thank you all for your help, @ScottWorld’s answer worked perfectly. Thank you so much!

[Shota_Kondo]

Thank you Justin, it worked like a charm!
It was something ive been struggling for a while, so thank you!