Formula help

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
.

=vlookup() returns a single value.

Can you drop the formula and just autofilter the data -- starts with "SA 60"?

You could use multiple formulas in multiple cells.

Chip Pearson explains how:
http://www.cpearson.com/Excel/TablesAndLookups.aspx
(Look for Arbitrary Lookups)

Since you're looking at the first 5 characters, you'll have to incorporate that
into the formula, too.

Mike wrote:

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
.


--

Dave Peterson

The Auto filter doesnt do it. I think because the data is in another sheet.


"Dave Peterson" wrote:

=vlookup() returns a single value.

Can you drop the formula and just autofilter the data -- starts with "SA 60"?

You could use multiple formulas in multiple cells.

Chip Pearson explains how:
http://www.cpearson.com/Excel/TablesAndLookups.aspx
(Look for Arbitrary Lookups)

Since you're looking at the first 5 characters, you'll have to incorporate that
into the formula, too.

Mike wrote:

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
.


--

Dave Peterson
.

I have tried several COUNTIF's in the formula =IF(ISNA(VLOOKUP($A8&"*",'SHIFT
D'!C$10:J$33),8,FALSE),"",VLOOKUP($A8&"*",'SHIFT D'!C$10:J$33),8,FALSE)) but
it keeps returning the the same information.


"Mike" wrote:

The Auto filter doesnt do it. I think because the data is in another sheet.


"Dave Peterson" wrote:

=vlookup() returns a single value.

Can you drop the formula and just autofilter the data -- starts with "SA 60"?

You could use multiple formulas in multiple cells.

Chip Pearson explains how:
http://www.cpearson.com/Excel/TablesAndLookups.aspx
(Look for Arbitrary Lookups)

Since you're looking at the first 5 characters, you'll have to incorporate that
into the formula, too.

Mike wrote:

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
.


--

Dave Peterson
.

The autofilter will keep the data in the same location -- it just hides the rows
you don't want to see.



Mike wrote:

The Auto filter doesnt do it. I think because the data is in another sheet.

"Dave Peterson" wrote:

=vlookup() returns a single value.

Can you drop the formula and just autofilter the data -- starts with "SA 60"?

You could use multiple formulas in multiple cells.

Chip Pearson explains how:
http://www.cpearson.com/Excel/TablesAndLookups.aspx
(Look for Arbitrary Lookups)

Since you're looking at the first 5 characters, you'll have to incorporate that
into the formula, too.

Mike wrote:

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
.


--

Dave Peterson
.


--

Dave Peterson