Lookup with multiple value return
 

I have a worksheet that has names & information in columns A-D. In the
adjacent columns it has blocks for each day of the month. Next to the names
each cell has a value of either "1", "L", or "T". I am trying to set up a
lookup formula to return all of the names in column A that have a cell value
of "L" depending on the corresponding day. Say:

A B C D E F G H I J K
Feb 2004 1 2 3 4 5 6 7
ADAMS D 3 Off 1 1 1 1 1 L L
JONHSON J 5 Sup 1 1 L L L L 1
WILLIAMS C 7 Road 1 L L L 1 1 1



I want it to tell me:

1 Feb:
2 Feb: Willaims
3 Feb: Johnson, Willaims
4 Feb: Johnson, Willaims
5 Feb: Johnson
6 Feb: Adams, Johnson
7 Feb: Adams

I am currently using this formula:

=IF('[workbook.xls]Jan 03:Dec 05'!2:2=TODAY(),LOOKUP("L",'[workbook.xls]Jan
03:Dec 05'!$2:$2,'[workbook.xls]Jan 03:Dec 05'!$A:$AK), FALSE())

Column 2 of "workbook.xls" is a hidden column with the proper date format
(2/1/05). Any suggestions?

You can't do that using vlookuo, there are some workarounds using a
combination of index and small but I would recommend to use a filter, either
auto (easier) or advanced. If you apply autofilter you can filter on L you
can then copy the visible
info somewhere else. To use the formula see

http://tinyurl.com/56nv4

--

Regards,

Peo Sjoblom


<Mike Quinn; <SrA; "USAF"
wrote in message ...
I have a worksheet that has names & information in columns A-D. In the
adjacent columns it has blocks for each day of the month. Next to the
names
each cell has a value of either "1", "L", or "T". I am trying to set up a
lookup formula to return all of the names in column A that have a cell
value
of "L" depending on the corresponding day. Say:

A B C D E F G H I J K
Feb 2004 1 2 3 4 5 6 7
ADAMS D 3 Off 1 1 1 1 1 L L
JONHSON J 5 Sup 1 1 L L L L 1
WILLIAMS C 7 Road 1 L L L 1 1 1



I want it to tell me:

1 Feb:
2 Feb: Willaims
3 Feb: Johnson, Willaims
4 Feb: Johnson, Willaims
5 Feb: Johnson
6 Feb: Adams, Johnson
7 Feb: Adams

I am currently using this formula:

=IF('[workbook.xls]Jan 03:Dec
05'!2:2=TODAY(),LOOKUP("L",'[workbook.xls]Jan
03:Dec 05'!$2:$2,'[workbook.xls]Jan 03:Dec 05'!$A:$AK), FALSE())

Column 2 of "workbook.xls" is a hidden column with the proper date format
(2/1/05). Any suggestions?

It will work but it's a bit ... complicated:

=IF(COUNTIF(OFFSET($D$2:$D$4,0,$A9),"=L")<B$8,".", OFFSET($A$1,SMALL(IF(OFFSET($D$2:$D$4,0,$A9)="L",R OW(INDIRECT("1:"&ROWS($A$2:$A$4))),""),B$8),0))


Upper left corner of this table is A8:
1 2 3 4
1 . . . .
2 WILLIAMS C . . .
3 JONHSON J WILLIAMS C . .
4 JONHSON J WILLIAMS C . .
5 JONHSON J . . .
6 ADAMS D JONHSON J . .
7 ADAMS D . . .


Ola Sandstrom

Note !
Since this is an Array formula you Must end the formulas by holding down
Ctrl+Shift and then press Enter.