Help

Using Find Function with a Lookup Field - Why exact match only?

Solved
Jump to Solution
3134 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Tom_Lucido
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello - I thought this would be rather easy to do, but something doesn’t seem to be working functionally. Any ideas why?

I’d like to get the position of the word “Education” within the Company Industry (Lookup) field, which is a lookup on a second table.

Why does it only work if it’s an exact match? And how can I adjust so that it also shows “1” for the fields with “Education Tech/Software”?

(NOTE: I found it works if I change "Company Industry (Lookup) to a single line text, but I need the lookup formula)

FIND("Education",{Company Industry (Lookup)})

screenshot 2020-03-26 at 12.24.16 PM

1 Solution

Accepted Solutions
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

I think there may be something going on behind the scenes here that will be a bit unintuitive – before I waste time explaining it, could I have you verify my hunch by changing your formula to this:

FIND("Education", {Company Industry (Lookup)}&'')

Let me know if that works as expected.

See Solution in Thread

3 Replies 3
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

I think there may be something going on behind the scenes here that will be a bit unintuitive – before I waste time explaining it, could I have you verify my hunch by changing your formula to this:

FIND("Education", {Company Industry (Lookup)}&'')

Let me know if that works as expected.

This worked. Thank you Jeremy!

Another solution I just found…

FIND("Education",ARRAYJOIN({Company Industry (Lookup)}))

Great! So I’m guessing you made the deduction already that behind the scenes, you were passing an array, rather than a string, to the FIND() function (which is expecting a string). If the array happens to have only a single element, then apparently Airtable’s formula engine is happy to coerce that array into a string for you. But if the array has more than one element in it, it seems the formula engine wants to leave it as an array.

My solution above just brute forces the array into a string by concatenation. But apparently, if your solution does indeed work, you can also explicitly declare the array as a comma delimited array, and that will allow it to be coerced into a string by the formula engine (which seems odd, but if it works, it works!).