I am trying to find a formula to search for text in a range of cells that if
found it would return data in another cell. For instance, Retun Jones in the
Sunday cell in the XXXX cell and so on. Also Tuesday it would return Doe and
Jones in the SA 61/1 XXXX cell on Monday.

Name Sunday Monday Tuesday Wednesday Thursday Friday
Smith W T DO DO W
PT 60
Doe T PT 61/1 PT 60 PT 61/1 DO DO
Jones PT 60 PT 61/1 W T PT 60 PT


PT 60 XXXX XXXX XXXX XXXX XXXX XXXX
PT61/1 XXXX XXXX XXXX XXXX XXXX XXXX

Hi Mike,

If your table is pretty much set up as shown, you can use a vlookup formula
to work this out, however you will need to make some adjustments.
(There are other ways to do this too.)

In a vlookup table the data you are looking for (in the first case PT60)
must be in the first column of the lookup table with the answer you are
looking for (Smith, Jones, etc.) somewhere after that. Yours are in front.
Therefore using your table I put the headers starting in A1 and spanning
across to column G just like you did, as follows:

A B C D E F
G
NAME SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY
Smith W T DO DO W
PT60
Doe T PT61/1 PT60 PT61/1 DO DO
Jones PT60 PT61/1 W T PT60 PT

In cell H2 I put =$A2 and dragged it down the length of the name list to
duplicate the names that are in A2-A4, THEN drag those across to column M and
down, so you have as many "smiths" etc. as you have days of the week. This
will give the lookup formula the names to "find". You can hide these columns
later if you don't want them to be seen. (You could just refernece the names
in cloumn H, but then you would need to adjust every formula in the VLOOKUP.
This is easier.)

In cells A6-A7 place the items you want to look up.

PT 60
PT61/1

In cell B6, directly under Sunday place the formula:

=IF(ISNA(VLOOKUP($A6,(B$2:H$4),7,FALSE)),"",VLOOKU P($A6,(B$2:H$4),7,FALSE))

Drag/copy it over to cell G6, then down to row 7. You can drag it down more
if you add items to find in column A.

In row 6 you should see the correct names in the correct cells.

I suggest recreating this to see if it works for you, then adjust it to your
needs.

Let me know if it works.

Squeaky.

"Mike" wrote:

I am trying to find a formula to search for text in a range of cells that if
found it would return data in another cell. For instance, Retun Jones in the
Sunday cell in the XXXX cell and so on. Also Tuesday it would return Doe and
Jones in the SA 61/1 XXXX cell on Monday.

Name Sunday Monday Tuesday Wednesday Thursday Friday
Smith W T DO DO W
PT 60
Doe T PT 61/1 PT 60 PT 61/1 DO DO
Jones PT 60 PT 61/1 W T PT 60 PT


PT 60 XXXX XXXX XXXX XXXX XXXX XXXX
PT61/1 XXXX XXXX XXXX XXXX XXXX XXXX

Yes, that worked, except I inserted rows in between rows 6 and 7 for more
than one name to add if more than one person is working that assignment like
on MONDAY it should return JONES and DOE for PT 61/1.

"Squeaky" wrote:

Hi Mike,

If your table is pretty much set up as shown, you can use a vlookup formula
to work this out, however you will need to make some adjustments.
(There are other ways to do this too.)

In a vlookup table the data you are looking for (in the first case PT60)
must be in the first column of the lookup table with the answer you are
looking for (Smith, Jones, etc.) somewhere after that. Yours are in front.
Therefore using your table I put the headers starting in A1 and spanning
across to column G just like you did, as follows:

A B C D E F
G
NAME SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY
Smith W T DO DO W
PT60
Doe T PT61/1 PT60 PT61/1 DO DO
Jones PT60 PT61/1 W T PT60 PT

In cell H2 I put =$A2 and dragged it down the length of the name list to
duplicate the names that are in A2-A4, THEN drag those across to column M and
down, so you have as many "smiths" etc. as you have days of the week. This
will give the lookup formula the names to "find". You can hide these columns
later if you don't want them to be seen. (You could just refernece the names
in cloumn H, but then you would need to adjust every formula in the VLOOKUP.
This is easier.)

In cells A6-A7 place the items you want to look up.

PT 60
PT61/1

In cell B6, directly under Sunday place the formula:

=IF(ISNA(VLOOKUP($A6,(B$2:H$4),7,FALSE)),"",VLOOKU P($A6,(B$2:H$4),7,FALSE))

Drag/copy it over to cell G6, then down to row 7. You can drag it down more
if you add items to find in column A.

In row 6 you should see the correct names in the correct cells.

I suggest recreating this to see if it works for you, then adjust it to your
needs.

Let me know if it works.

Squeaky.

"Mike" wrote:

I am trying to find a formula to search for text in a range of cells that if
found it would return data in another cell. For instance, Retun Jones in the
Sunday cell in the XXXX cell and so on. Also Tuesday it would return Doe and
Jones in the SA 61/1 XXXX cell on Monday.

Name Sunday Monday Tuesday Wednesday Thursday Friday
Smith W T DO DO W
PT 60
Doe T PT 61/1 PT 60 PT 61/1 DO DO
Jones PT 60 PT 61/1 W T PT 60 PT


PT 60 XXXX XXXX XXXX XXXX XXXX XXXX
PT61/1 XXXX XXXX XXXX XXXX XXXX XXXX

here is the formula as of now after applying it to my application.
=IF(ISNA(VLOOKUP($A8,('SHIFT D'!C$10:J$33),8,FALSE)),"",VLOOKUP($A8,('SHIFT
D'!C$10:J$33),8,FALSE))
It is written in sheet A, cell B8. A8 reads "SA 60". In sheet "Shift D" the
cells that have "SA 60" also contain more data, i.e. "SA 60 8A-4P". How would
you have it search for only the "SA 60" part? Also, I need it to contiune
looking for more than one person an return that in the cell beneth cell B9
and so on, if there is more than no person working that assignment.

"Mike" wrote:

Yes, that worked, except I inserted rows in between rows 6 and 7 for more
than one name to add if more than one person is working that assignment like
on MONDAY it should return JONES and DOE for PT 61/1.

"Squeaky" wrote:

Hi Mike,

If your table is pretty much set up as shown, you can use a vlookup formula
to work this out, however you will need to make some adjustments.
(There are other ways to do this too.)

In a vlookup table the data you are looking for (in the first case PT60)
must be in the first column of the lookup table with the answer you are
looking for (Smith, Jones, etc.) somewhere after that. Yours are in front.
Therefore using your table I put the headers starting in A1 and spanning
across to column G just like you did, as follows:

A B C D E F
G
NAME SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY
Smith W T DO DO W
PT60
Doe T PT61/1 PT60 PT61/1 DO DO
Jones PT60 PT61/1 W T PT60 PT

In cell H2 I put =$A2 and dragged it down the length of the name list to
duplicate the names that are in A2-A4, THEN drag those across to column M and
down, so you have as many "smiths" etc. as you have days of the week. This
will give the lookup formula the names to "find". You can hide these columns
later if you don't want them to be seen. (You could just refernece the names
in cloumn H, but then you would need to adjust every formula in the VLOOKUP.
This is easier.)

In cells A6-A7 place the items you want to look up.

PT 60
PT61/1

In cell B6, directly under Sunday place the formula:

=IF(ISNA(VLOOKUP($A6,(B$2:H$4),7,FALSE)),"",VLOOKU P($A6,(B$2:H$4),7,FALSE))

Drag/copy it over to cell G6, then down to row 7. You can drag it down more
if you add items to find in column A.

In row 6 you should see the correct names in the correct cells.

I suggest recreating this to see if it works for you, then adjust it to your
needs.

Let me know if it works.

Squeaky.

"Mike" wrote:

I am trying to find a formula to search for text in a range of cells that if
found it would return data in another cell. For instance, Retun Jones in the
Sunday cell in the XXXX cell and so on. Also Tuesday it would return Doe and
Jones in the SA 61/1 XXXX cell on Monday.

Name Sunday Monday Tuesday Wednesday Thursday Friday
Smith W T DO DO W
PT 60
Doe T PT 61/1 PT 60 PT 61/1 DO DO
Jones PT 60 PT 61/1 W T PT 60 PT


PT 60 XXXX XXXX XXXX XXXX XXXX XXXX
PT61/1 XXXX XXXX XXXX XXXX XXXX XXXX

=vlookup() supports wild cards, so you could use:

=IF(ISNA(VLOOKUP($A8&"*",'SHIFT D'!C$10:J$33),8,FALSE),"",
VLOOKUP($A8&"*",'SHIFT D'!C$10:J$33),8,FALSE))



Mike wrote:

here is the formula as of now after applying it to my application.
=IF(ISNA(VLOOKUP($A8,('SHIFT D'!C$10:J$33),8,FALSE)),"",VLOOKUP($A8,('SHIFT
D'!C$10:J$33),8,FALSE))
It is written in sheet A, cell B8. A8 reads "SA 60". In sheet "Shift D" the
cells that have "SA 60" also contain more data, i.e. "SA 60 8A-4P". How would
you have it search for only the "SA 60" part? Also, I need it to contiune
looking for more than one person an return that in the cell beneth cell B9
and so on, if there is more than no person working that assignment.

"Mike" wrote:

Yes, that worked, except I inserted rows in between rows 6 and 7 for more
than one name to add if more than one person is working that assignment like
on MONDAY it should return JONES and DOE for PT 61/1.

"Squeaky" wrote:

Hi Mike,

If your table is pretty much set up as shown, you can use a vlookup formula
to work this out, however you will need to make some adjustments.
(There are other ways to do this too.)

In a vlookup table the data you are looking for (in the first case PT60)
must be in the first column of the lookup table with the answer you are
looking for (Smith, Jones, etc.) somewhere after that. Yours are in front.
Therefore using your table I put the headers starting in A1 and spanning
across to column G just like you did, as follows:

A B C D E F
G
NAME SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY
Smith W T DO DO W
PT60
Doe T PT61/1 PT60 PT61/1 DO DO
Jones PT60 PT61/1 W T PT60 PT

In cell H2 I put =$A2 and dragged it down the length of the name list to
duplicate the names that are in A2-A4, THEN drag those across to column M and
down, so you have as many "smiths" etc. as you have days of the week. This
will give the lookup formula the names to "find". You can hide these columns
later if you don't want them to be seen. (You could just refernece the names
in cloumn H, but then you would need to adjust every formula in the VLOOKUP.
This is easier.)

In cells A6-A7 place the items you want to look up.

PT 60
PT61/1

In cell B6, directly under Sunday place the formula:

=IF(ISNA(VLOOKUP($A6,(B$2:H$4),7,FALSE)),"",VLOOKU P($A6,(B$2:H$4),7,FALSE))

Drag/copy it over to cell G6, then down to row 7. You can drag it down more
if you add items to find in column A.

In row 6 you should see the correct names in the correct cells.

I suggest recreating this to see if it works for you, then adjust it to your
needs.

Let me know if it works.

Squeaky.

"Mike" wrote:

I am trying to find a formula to search for text in a range of cells that if
found it would return data in another cell. For instance, Retun Jones in the
Sunday cell in the XXXX cell and so on. Also Tuesday it would return Doe and
Jones in the SA 61/1 XXXX cell on Monday.

Name Sunday Monday Tuesday Wednesday Thursday Friday
Smith W T DO DO W
PT 60
Doe T PT 61/1 PT 60 PT 61/1 DO DO
Jones PT 60 PT 61/1 W T PT 60 PT


PT 60 XXXX XXXX XXXX XXXX XXXX XXXX
PT61/1 XXXX XXXX XXXX XXXX XXXX XXXX


--

Dave Peterson

Thanks Dave and Squeaky.
Is there a way to continue looking for more people who are working the
assignment in A8 and list it under in the same column?

"Dave Peterson" wrote:

=vlookup() supports wild cards, so you could use:

=IF(ISNA(VLOOKUP($A8&"*",'SHIFT D'!C$10:J$33),8,FALSE),"",
VLOOKUP($A8&"*",'SHIFT D'!C$10:J$33),8,FALSE))



Mike wrote:

here is the formula as of now after applying it to my application.
=IF(ISNA(VLOOKUP($A8,('SHIFT D'!C$10:J$33),8,FALSE)),"",VLOOKUP($A8,('SHIFT
D'!C$10:J$33),8,FALSE))
It is written in sheet A, cell B8. A8 reads "SA 60". In sheet "Shift D" the
cells that have "SA 60" also contain more data, i.e. "SA 60 8A-4P". How would
you have it search for only the "SA 60" part? Also, I need it to contiune
looking for more than one person an return that in the cell beneth cell B9
and so on, if there is more than no person working that assignment.

"Mike" wrote:

Yes, that worked, except I inserted rows in between rows 6 and 7 for more
than one name to add if more than one person is working that assignment like
on MONDAY it should return JONES and DOE for PT 61/1.

"Squeaky" wrote:

Hi Mike,

If your table is pretty much set up as shown, you can use a vlookup formula
to work this out, however you will need to make some adjustments.
(There are other ways to do this too.)

In a vlookup table the data you are looking for (in the first case PT60)
must be in the first column of the lookup table with the answer you are
looking for (Smith, Jones, etc.) somewhere after that. Yours are in front.
Therefore using your table I put the headers starting in A1 and spanning
across to column G just like you did, as follows:

A B C D E F
G
NAME SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY
Smith W T DO DO W
PT60
Doe T PT61/1 PT60 PT61/1 DO DO
Jones PT60 PT61/1 W T PT60 PT

In cell H2 I put =$A2 and dragged it down the length of the name list to
duplicate the names that are in A2-A4, THEN drag those across to column M and
down, so you have as many "smiths" etc. as you have days of the week. This
will give the lookup formula the names to "find". You can hide these columns
later if you don't want them to be seen. (You could just refernece the names
in cloumn H, but then you would need to adjust every formula in the VLOOKUP.
This is easier.)

In cells A6-A7 place the items you want to look up.

PT 60
PT61/1

In cell B6, directly under Sunday place the formula:

=IF(ISNA(VLOOKUP($A6,(B$2:H$4),7,FALSE)),"",VLOOKU P($A6,(B$2:H$4),7,FALSE))

Drag/copy it over to cell G6, then down to row 7. You can drag it down more
if you add items to find in column A.

In row 6 you should see the correct names in the correct cells.

I suggest recreating this to see if it works for you, then adjust it to your
needs.

Let me know if it works.

Squeaky.

"Mike" wrote:

I am trying to find a formula to search for text in a range of cells that if
found it would return data in another cell. For instance, Retun Jones in the
Sunday cell in the XXXX cell and so on. Also Tuesday it would return Doe and
Jones in the SA 61/1 XXXX cell on Monday.

Name Sunday Monday Tuesday Wednesday Thursday Friday
Smith W T DO DO W
PT 60
Doe T PT 61/1 PT 60 PT 61/1 DO DO
Jones PT 60 PT 61/1 W T PT 60 PT


PT 60 XXXX XXXX XXXX XXXX XXXX XXXX
PT61/1 XXXX XXXX XXXX XXXX XXXX XXXX


--

Dave Peterson
.