Search code examples
excelif-statementexcel-formulavlookupworksheet-function

Function using IF and AND and OR on a range of cells

I am trying to use an IF function in combination with AND and OR. I have several columns with text and I want a 1 or 0 (true or false):

if (cell1:cell10 == 'stringA' AND ('stringB' OR 'stringC') then return '1'

otherwise, return '0'.

Solution

  • Beware, not properly tested by me!: 

    =--AND(NOT(ISERROR(VLOOKUP("stringA",A:A,1,0))),OR(NOT(ISERROR(VLOOKUP("stringB",A:A,1,0))),NOT(ISERROR(VLOOKUP("stringC",A:A,1,0)))))

    Note I have covered the entire column cell1:cell10 (I assumed ColumnA) but range can be restricted to A1:A10 say, if desired.