How to search with an input in the entire column instead of the row

Hello community, I need help with a logic, I am learning about Glide and have made great progress, but I am stuck in this context:

I have a search to be done in a table, this table has the following composition:


  • The idea is to have a text entry (input) where I will enter the CNPJ (number)
  • If the input is not found, that is, the entered CNPJ is not equal to any CNPJ in the database, I will return = Available client for negotiation.
  • If the input is found, that is, the entered CNPJ is equal to some CNPJ that is already in the database, I need to make another verification in the Status column.
  • If the CNPJ is found, I search the status column that can have two statuses: lost or won.
  • If the status is = lost, I will return = Lost client, available for negotiation.
  • If the status is = won, I will return = Already our client.

Google Sheets Formula:

  • I used the following formula in GS and it worked smoothly:
=IF(ISBLANK(G2),"",IFERROR(IF(VLOOKUP(G2,API!A:E,3,FALSE)="won","Already our client";IF(VLOOKUP(G2,API!A:E,3,FALSE)="lost","Lost client, available for negotiation")),"Available for negotiation"))


  • I tried to use the logic in GS, but there are 40,000 CNPJs, which makes the search very slow (discarded).
  • I tried to use a search logic (input) + IF-THEN-ELSE column, but the input seems to search line by line and not the entire column, if you know how to do this.
  • I tried to use common lookups, but I did not know how to relate the first CNPJ search with the conditions in case the CNPJ is found and I need to verify the status.

I would do it something like this:

  • Create a separate single row table to use as the source for your query input
  • Add a RowID column to that table, and a User Specific Text column to accept your Text Input (CNPJ value)
  • Create a single relation column that matches the entered CNPJ value with the CNPJ column in your data table
  • Add a Lookup column that fetches the Status value via the Relation column
  • Finally, add an if-then-else column using the following:
    – If Relation column is empty, then ‘Available client for negotiation’
    – If Lookup column is lost, then ‘Lost client, available for negotiation’
    – If Lookup column is won, then ‘Already our client’

Hey @Darren_Murphy , it perfectly solved what I needed, I really appreciate the help the glide community has been incredible in this process of learning with the tool. Thanks!

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.