Can you make the FIND function case insensitive?

[bloesch1]

Hi,

I've got a formula (below) that I believe the FIND function is causing it to return case sensitive results. Is there any way to modify the formula to make it case insensitive?

=SUMPRODUCT(--ISNUMBER(FIND(G5," "&Tasks&" ")))

Thanks,
Brian

 

[Andrew Poulsom]

The SEARCH function isn't case-sensitive.

 

[bloesch1]

Perfect- I didn't know about that one.

Thank you.

 

[Scott Huish]

Not sure why you need SUMPRODUCT, does this do what you want?

=ISNUMBER(SEARCH(G5," "&Tasks&" "))+0

 

[bloesch1]

That shortened formula without the SUMPRODUCT did not return the correct result.

On a slightly related note, I'm struggling with the original formula to accept multiple ranges. The current range is only TASKS, do you know how I could extend it to TASKS and TASKS_2?

Thanks again- you guys that help answer these questions are lifesavers (and not candy...)

 

[Matty]

Hi,

A short illustration of your data set up might help us to point you in the right direction.

Matty

 

[Scott Huish]

Is this what you want?

=SUMPRODUCT(COUNTIF(G5," "&Tasks&" ")+COUNTIF(G5," "&tasks_2&" "))

 

[bloesch1]

I'm afraid that's not quite what I need. I find that I have to use the ISNUMBER and SEARCH Functions because The criteria in the example cell of G5 will not always match the data entered into the range (TASKS and TASKS_2) because a cell may have more than one value.

Currently my full formula is...

=IF(G5=0,0,SUMPRODUCT(--ISNUMBER(SEARCH(G5," "&Tasks&" "))))

The context behind the code is that the people on this tab have certain available tasks that are listed. G5 contains such as task. However, Person A can work on the task in G5, but can also work on tasks G6 and G7. In this case, the user would simply put those corresponding values in the TASK range for person A into one cell. The SEARCH Formula picks that up because of the multiple values in the cell.

Now, there is a copy of this sheet for another group. Originally group 2 could not work on group 1's tasks. They can now. So if Person X in group 2 wants to work on task G5 (from group 1), I need to account for that person in the above formula.

However, I'm having issues modifying the formula to put an addition for the range TASK_2. Hopefully this helps shed some light on the issue.

Thanks again!

 

[Scott Huish]

Cell G5 has more than one value?

Can you post a sample and expected results?

 

[bloesch1]

Sure- here is a simplified example (my real copy includes rates, hours and distribution of weeks which is irrelevant to this discussion)

LEGAL DEPARTMENT
Person AssignedTasks Task List Task Description
P1 A A Chase Ambulances
P2 A,B B Sue Somebody


Count COUNTTotal
A 2 (this is where the formula in question exists)
B 1

In this example for the legal department:
G5 would be equal Chase Ambulances under Task List
The Named Range "Tasks" would be the cells under AssignedTasks

Again, my goal is to modify the current formula to add other named ranges in the COUNTTotal column. On a seperate tab for the accounting department, I would list out accounting tasks. However, if an accountant helps perform a legal task (perhaps as the driver for ambulance chasing), I would want to reference the cell for that task (in this case: G5)


I should also point out that the first part of my formula: =IF(G5=0,0 eliminates a calculation error if the cell is empty.

Please let me know if this is a good enough example.

Thanks!