Problem Returning Mulitple Lookup Values
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. |
Problem Returning Mulitple Lookup Values
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 wrote in message ... 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. |
All times are GMT +1. The time now is 01:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com