How to select a VLOOKUP row based on multiple column values

1
votes

We have 11 columns (Columns B through L) of codes that I need to select based on a VLOOKUP from another sheet. IF ANY of the column values are "HI" or "EXT", I need to keep the record, if ALL of the column values are "M" I can exclude it. Column A is my LOOKUP list. enter image description here

Right now the best I can come up with is 11 nested =IF(VLOOKUP(...) statements to set an inclusion flag, but if there's a way to SUM a TRUE/FALSE flag based on equality to the value "M" across all 11 columns...I've not had success finding that.

Any ideas?

1
excelexcel-formulavlookup

1 Answers

0
votes

This can be solved in two steps:

  1. For columns B-L, the formula needs to be your VLookup formula (which you didn't put here) and ="M" at the end of it, which will result in a binary true/false value.
  2. Then, in column M, simply do a logical AND using the AND function across B-L for each row e.g. =AND(B1:L1)

Another option, if you wish to keep the display format the same, is to do an array formula.

  1. Enter =IF(AND(B1:L1="M"), "KEEP", "EXCLUDE"), then press CTRL+SHIFT+ENTER and it will add curly braces to it, meaning it calculates an array value. The resulting formula in the cell will be {=IF(AND(B1:L1="M"), "KEEP", "EXCLUDE")}. I tested, and it appeared to work as expected.