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

[Lucas_Soares]

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:

Context:

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

Attempts:

• 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.

[Darren_Murphy]

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’

[Lucas_Soares]

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!