Returning multiple values from VLOOKUP

[towners]

Hi all,

I have a VLOOKUP function that searches a concatenanted field for a unique occurance of date, line and shift and populates a report with data such as productivity and quality for that match.

I've now been asked to add batch information to the report, my problem is that whereas I know the date, line and shift I do not know the batch numbers or how many batches will be produced until after the event.

I want my report to detail each batch and productivity and quality figures, with my current code I only display the first unique match.

I know that there is a limit to the amount of batches that can be produced per shift so I can make space for them in my report (or perhaps some clever person can help me to modify my report to cater for 1 or many batches?) my challenge is writing the code...

Here is my VLOOKUP code that poulates my productivity cell in the report

=IF(ISBLANK(VLOOKUP($B$1&"Line "&$A$5&$B5,'Daily Data'!$A:$AA,24,FALSE)),"",VLOOKUP($B$1&"Line "&$A$5&$B5,'Daily Data'!$A:$AA,24,FALSE))

Batch information is in ROW "X"

My report columns are Line, Shift, Batch, Qty produced, Productivty

It goes without saying I'd be very grateful of some advice/ solution.

Many thanks

Paul

 

[Joseph.Marro]

This might help you as long as you are not using 2007.

http://www.mrexcel.com/forum/showthread.php?t=313227

If you are using 2007 I have not been able to get it to work but maybe someone here can help fix it. I do know that it works in 2003.

 

[towners]

Thanks for the reply, I like the function and this will help however I get a NAME error, so presumably excel isn't recognising the new function..?

What I need to do now is sum all the results from my lookup (i.e. sum the productivity for each batch for each date, line and shift. My currect code only finds the first match.

Can anyone help me with this?

many thanks

Paul