# Returning multiple values from VLOOKUP

#### towners

##### Board Regular
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

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### towners

##### Board Regular
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

Last edited:

Replies
1
Views
496
Replies
1
Views
189
Replies
1
Views
345
Replies
7
Views
2K
Replies
0
Views
198

1,191,686
Messages
5,988,073
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back