Problem Returning Mulitple Lookup Values

[djrobb03]

I have a workbook with two sheets, sheet2 is a data sheet and sheet1
is a summary of information from sheet2 which performs calculations on
information from sheet2.

Sheet1 is layed out with a listing of names starting in G6:G10 (i.e.
team member names Derek, John, etc.) and cells I3:IJ3 contain a
listing of dates, both correspond to Sheet2 (assume he same data
ranges for now on both sheets). The problem I am having is returning
a data value to sheet1 for multiple instances of a team member name on
sheet2. For instance "Derek" may show up 4 times in G6:G10 and
associate with different dates on sheet2 but using vlookup I can only
return the first row where "Derek" is found. The formula is copied
accross the sheet to correspond with the various dates.

I would like to return all of the data instances based on the date for
a particular team member.
The formula I currently started with is =IF(ISERROR(VLOOKUP(MANPOWER!
$G6, GanttChart!$D$3:$IU$94, 1, FALSE)), "-", HLOOKUP(I$5, GanttChart!
$D$3:$IU$94, MATCH($G6,GanttChart!$D$3:$D$94,))) where Manpower is
sheet1 and GanttChart is sheet2 in my example above.

Any help is apprciated. I can email a copy of the spreadsheet upon
request.

 

[Arvi Laanemets]

Hi

There is a way to do this using array formulas, but I myself prefer to use
an additional key column on source sheet for such cases.

You must have some identifier for every team member (my advice is to use
some fixed lenth numeric string, like "001", "002", etc.) - probably stored
on sheet with team member list (additionally you can use team member list
as source for data validation dropdowns on sheet2, and also as source for
team member list on sheet1 too).

On sheet 2 add a column as leftmost one, and generate an unique identifier
for every row, composed from team member code, and fixed length order number
of entry for this team member. I.e. the 1st entry for team member "003" gets
code "003001", the second entry gets code "003002" etc. A possible formula
on fly:
A6=IF($G6="","",VLOOKUP($G6,MemberList,2,0) &
TEXT(COUNTIF($G$6:$G6,$G6),"000"))
(After creating the formula and copying it down you can hide the index
column)

Now in sheet1 you use VLOOKUP formula to fill table with dates - something
like:
=IF(ISERROR(VLOOKUP(MemberCode &
TEXT(ROW()-7,"000"),SourceRange,1,0)),"",VLOOKUP(MemberCode &
TEXT(ROW()-n,"000"),SourceRange,x,0))
,where x is the number of column with dates in SourceRange on sheet2 (and I
advice to define SourceRange as an dynamic named range).

Arvi Laanemets