View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
zvkmpw zvkmpw is offline
external usenet poster
 
Posts: 153
Default Vlookup of specific text, then in consecutive rows

Is there a way to somehow wildcard that formula
to also find any cased relief ?


Yes, easily.

First, look at the formula for column A. It can be paraphrased this
way:
If
(a) the row is to be excluded
Then
(b) return ""
Otherwise
(c) return the next higher unused number.
In this example, (a) is the ISERROR(...) test. The (a) part of the
solution is the *only* thing that filters the original data so that
just the desired rows show up in the final list.

For different examples, (a) can be any test of any values in the row.
One can change only (a), leaving everything else the same, and achieve
all sorts of filtering possibilities.

For example, to make the test case-insensitive (a) might be:
ISERROR(FIND("relief",LOWER(MasterTab!O21)))

For another example, to disallow duplicate names, (a) might be:
OR(ISERROR(...),
COUNTIF(MasterTab!BB$21:BB21,MasterTab!BB21)1)

Modify to suit.