How to search for occurrence of multiple texts in same row/cell

Hi,

I have a lookup table in which names of students and the planned session dates are available.
I need to create a chart that has Student Name and Each date of month. The chart should have value of 1 wherever a match is found in the look up table.

I am wondering if combination of searching for multiple strings (student name and date ) will help somehow.
The below image explains my problem

StudentTimeTable.png

Use this formula...
in your result table and drag it horizontally ..

Formula:

=IF(LARGE(IFERROR(FIND(J2,$E$3:$E$5),0),1),1,0)

Use Ctrl + Shift + enter to enter the formula...


Say thanks, click *

Hi Vikas,

Sorry, but I didn't understand your solution. Can you explain please.
My understanding is that we'll need to FIND for both the date and the student name. But your solution has just single FIND.

Originally Posted by coolfundaz

Hi Vikas,

Sorry, but I didn't understand your solution. Can you explain please.
My understanding is that we'll need to FIND for both the date and the student name. But your solution has just single FIND.

Hi coolFundaz...

you don't need to find student name...
just change the cell reference each time for each student and drag it horizontally...

For instance..
for John..

try this formula... in E5 and drag it horizontally till P5

Formula:

=IF(LARGE(IFERROR(FIND(E4,$B$5:$B$6),0),1),1,0)

and press ctrl shift enter....

Say thanks, click *

Arrived at the solution

This formula worked. Copied this one as Array (Ctrl + Shift + Enter) formula in all the cells
=IF(SUMPRODUCT(IFERROR(SEARCH(E$4,$B$5:$B$12),0),IFERROR(SEARCH($D5,$A$5:$A$12),))>0,1,"")

It shows 1 only where it should


Ford - Thanks for your suggestion. Will follow it for future posts