Help

Re: IF FIND- help with formula

Solved
Jump to Solution
2688 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Katy_Richards
5 - Automation Enthusiast
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions
ScottWorld
18 - Pluto
18 - Pluto

@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} & ""

See Solution in Thread

9 Replies 9

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
18 - Pluto
18 - Pluto

@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
4 - Data Explorer
4 - Data Explorer

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!

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"
)

Screen Shot 2021-10-22 at 6.44.10 AM

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")
)

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.

Sorry, I skimmed the original a bit too quickly.

Katy_Richards
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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