1

I have this sample of a spreadsheet to simplify what I'm trying to do:

spreadsheet

In "Formula 1" I'm using a VLOOKUP function within an IF condition to assign value_if_true (and value_if_false) whether the result of the VLOOKUP function, specifically each value in column C, contains the specific text "rock". This gives me no problem when the IF criteria exactly matches the content of the indexed column, as we can see in cell F3.

The problem rises when I have to search for a partial match, as other values in column C contain not only the "rock" text. Using wildcards directly in the IF criteria doesn't work so, searching for this kind of operation, this thread uses COUNTIF to check for specific partial text using wildcards.

I tried to replicate this with my case just substituting the "range" argument of COUNTIF with my VLOOKUP function, as seen in "Formula 2", but in this case the system detects an error within the formula that cannot elaborate. The only thing that I can guess is that the COUNTIF function can't digest a VLOOKUP function as "range".

What other functions or workarounds could I use to instruct the IF condition in returning me the value "true" every time the word "rock" appears in column C?

Markus Meyer
  • 1,370
  • 5
  • 11
  • 17
NorthSon
  • 127
  • 5
  • Although the answers directly respond to your ask, I think the data is the real potential issue you need to solve. For example what should a search for "sand" result in ID 1 being true or false? Maybe you just end up having 5 categories and so a partial/fuzzy lookup is unnecessary. – gns100 Feb 15 '23 at 19:09

2 Answers2

1

To get they function to work with VLOOKUP, you may need to combine it with the AND function, this may be what you are after:

=IF(AND(ISTEXT(VLOOKUP(E2,A2:C7,3,FALSE)),ISNUMBER(SEARCH("*rock*",C2))),TRUE,FALSE)

This is searching for you ID, then looking to see if the condition contains 'rock'.

enter image description here

Dr_Xunil
  • 326
  • 3
  • 14
  • Thank you! That was the answer I was searching for. It only missed a further VLOOKUP as the second argument of the SEARCH function, so that the function will search the proper C cell corresponding to a specific ID object – NorthSon Feb 16 '23 at 08:45
0

If I understand your question correctly, the following formula will return 'TRUE' if it ever finds the word "rock" in Column C (NOTE: this searches line by line, C2, C3, and so on): =IF(ISNUMBER(SEARCH("*rock*",C2)),TRUE,FALSE)

enter image description here

Dr_Xunil
  • 326
  • 3
  • 14
  • Thank you, however, this way is not filtering data through a VLOOKUP function. This sheet is simplified, but for my work, I have the first part of the sheet (columns A:C) with thousands of records and I've selected only a few of them based on certain criteria (through the ID column). Then for the IDs I've selected, I have to assign values to (in this case in column F) whether the soil has certain characteristics or not, thus using a VLOOKUP inside an IF. – NorthSon Feb 15 '23 at 17:43