Left Lookup to Return Multiple Values Given 2 Criteria

Hi All,

THis is way out of my league so help would very gratefully appreciated.

On the attached workbook, I have 2 sheets - a DATA sheet with the lookup table and a PLAN sheet where I wish to populate a list of Job Ref given 2 criteria on that sheet - Date and Crew. The formular I require is in E6 to E13 on the Plan sheet given the criteria in cells B2 (date) and B6 (crew). I have pasted in the expected results given the 2 criteria shown which returns 4 jobs.

Also, the data set on the DATA sheet will increase/decrease daily as the sheet is updated.

Hope this makes sense. Thanks.

Capture.JPG

Hi, So given the 2 criteria on the PLAN sheet, the list needs to search for all matching criteria in columns AX (date) and AY (crew) on the DATA sheet and list the values in column A.

You've removed a lot of data from your sample file, but I assume that column BG is the next available column on the Data sheet, so put this formula in BG2:

=IF(OR(AX2="",AY2=""),"-",AY2&"_"&AX2&"_"&COUNTIF(BG$1:BG1,AY2&"_"&AX2&"*")+1)

Copy this down to beyond the bottom of your data, to accommodate more data being added. Then you can have this formula in cell E6 of the Plan sheet:

=IFERROR(INDEX(DATA!$A:$A,MATCH($B$6&"_"&E$4&"_"&$D6,DATA!$BG:$BG,0)),"")

and you can copy this down to E13. Then you can copy the block E6:E13 across into H6, K6, N6 etc. to complete that week.

If you want to have another crew in B15, then you can copy the block D6:W13 into D15, and while the block is still highlighted, do CTRL-H (Find & Replace) and change B$6 to B$15. Repeat for blocks further down.

Hope this helps.

Pete

Formula:

Please Login or Register  to view this content.

Use this formula in E6 with Ctrl+Shift+Enter and copy it down.

Thanks
Ankur

A thousand thanks for this formula which works fantastically. Not quite sure where the "ZZZZZZZ" comes in.

Thanks Pete_UK for giving this a go.... I did think about using a Helper column. However, nflsales has nailed it.