0

I can solve this in VBA easy enough but it's bugging me that I can't think of a formula combination to solve it natively in Excel.

I need to know if a value in column A is present anywhere in columns B to D. It could be in any row and in any column.

Index / match won't work as far as I know because both the row and the column are variable. Ideally I need something like Find or Search that takes a multi-dimensional array, not just one row, column or string.

  • How would I know how much to offset? – Absinthe Jun 21 '17 at 13:29
  • Do you want to know simply true or false if it is in either column? Or if it does exist, do you want the first instance found? There could be multiple finds – Eric F Jun 21 '17 at 13:32
  • Thanks, just a true / false, no need for the location or to return the cell value – Absinthe Jun 21 '17 at 13:39
  • Sorry for the first comment, I commented too quickly before reading but this can be achieved using the COUNTIF. If the count = 0 then not found, and if found the count will be > 0 – Eric F Jun 21 '17 at 13:41
  • Ha! Of course, can't believe I didn't think of that. Thank you. – Absinthe Jun 21 '17 at 13:42
4

Use the following formula for a boolean response:

=IF(COUNTIF(B:D,A1)=0,FALSE,TRUE)

The count will be 0 if not found in the range and greater than 0 if found. The result will be TRUE in the cell if found, and FALSE if not found.

  • Hi, is it possible to return which column the value is found in, rather than return TRUE/FALSE ? if so I have an open question superuser.com/questions/1456521/… – PeterH Jul 5 '19 at 13:53
  • FYI you could shorten this to =COUNTIF(B:D,A1)=0 for great answer tho ! +1 – PeterH Jul 5 '19 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.