Cannot commit partial inquiry in excel formula (cannot use VBA)

Question

I have an excel spreadsheet that will be ran on VM's that cannot run VBA code. So, all features have to be created in excel formulas.

=MATCH(INDIRECT("G"&ROW()), LISTS!$C$2:$C$25, 0)

This is the formula I am using with conditional formatting. I need a cell to highlight if any word from a range of cells on second sheet match any portion of current cell. For example. I have "alpha 1b ny" in cell needing format and I have a range that contains "alpha, bravo, Charlie, delta". This should highlight because alpha matches. With above formula if cell only contains alpha it will highlight. If it contains anything else it is ignored. I've tried adding "*" before and after indirect("G"&row()). Any help would be greatly appreciated.

Michal · Accepted Answer · 2018-11-06 21:30:23Z

0

 '=IFERROR(INDEX(List,MATCH(1,COUNTIF(A1,"*"&List&"*"),0)),"")

'List' is a named range with your list of words.

Edit - as an array formula it has to be entered with Ctrl-Shift-Enter:

edited Nov 6, 2018 at 21:30

answered Nov 5, 2018 at 23:17

Michal

7,6744 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Zebriah Over a year ago

Tried this to no avail. This formula is going in a conditional format and needs to pull from current cell in range. for example "alpha 1b ny" is in a column of 100 cells with similar phrases and all of them need to be compared and highlighted based on partial match from what's in LIST.

Zebriah Over a year ago

I believe to add clarification to what I want to do is this. I have 2 columns: one Input the other Reference. In reference I have Alpha, Bravo, Charlie. In Input an individual will enter a string like "alpha 1b ny". Because it has Alpha in it it will highlight. In your formula COUNTIF(A1,""&List&"") has range and value swapped? I've tried using wildcards on ranges and it doesn't work. If this is possible then I should be able to get this to work.

Zebriah Over a year ago

Found the solution. =SUMPRODUCT(--ISNUMBER(SEARCH(IF(LISTS!$C$2:$C$25<>"",LISTS!$C$2:$C$25),$G5)))>0

Michal Over a year ago

Forgot to mention it's an array formula, so should be entered with Ctrl-Shift Enter - sorry.

Collectives™ on Stack Overflow

Cannot commit partial inquiry in excel formula (cannot use VBA)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related