# Re: IF FIND- help with formula

Solved
1514 0
cancel
Showing results for
Did you mean:
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
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} & ""
``````
9 Replies 9
10 - Mercury

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:

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} & ""
``````
4 - Data Explorer

Hi guys, I was about to post a similar question where i saw @Katy_Richards question.

``````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!

18 - Pluto

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

18 - Pluto

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")
)
``````
18 - Pluto

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.

18 - Pluto

Sorry, I skimmed the original a bit too quickly.

5 - Automation Enthusiast

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

4 - Data Explorer

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