Formula help

[Mike]

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

 

[Squeaky]

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)),"",VLOOKUP($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]

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

 

[Mike]

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.

 

[Dave Peterson]

=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]

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]

=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]

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

 

[Mike]

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.

 

[Dave Peterson]

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

 

[Dave Peterson]

Take a look at Chip's site.

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.