Skip to main content
Solved

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

  • March 26, 2020
  • 3 replies
  • 25 views
T

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

Best answer by Jeremy_Oglesby

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.

    3 replies

    J
    Forum|alt.badge.img+18

    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.

    T
    • Tom_Lucido
    • Author
    • New Participant
    • March 26, 2020

    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)}))
    N
    J
    Forum|alt.badge.img+18

    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!).

    Terms & ConditionsAccessibility statement