vlookups with multiple returns

[theboywhelp]

Hi

I am trying to write a formula that will give more than 1 return for a vlookup statement.
The info i am looking up is sku code (col a) and expiry date (col b).
There are more than one entry for some skus , but I am only getting returned the first expiry date.
Can anyone help?

[martindwilson]

you need an array lookup see attached formula in d2 is an array confirmed with ctrl+shift+enter then dragged down

[theboywhelp]

Thanks Martin.

Ideally i would want the info put into the rows rather than the columns following the data (as the lookups are in a table too) im afraid I can't get that to work.

Can you explain what the formula is doing?

[martindwilson]

for across row you need
=IF(COLUMN()-3>COUNTIF($A$1:$A$2000,$C2),"",INDEX($B$1:$B$2000,SMALL(IF(Sheet1!$A$1:$A$2000=$C2,ROW($A$1:$A$2000),""),COLUMN()-3)))
the -3 after column() ,twice, depends on which col you wish to start the array from in this case col D so its 3 (col D=4 -3 to make the value 1 )so if it started in column M it would be -12 (13-12=1)
just put it in d2 confirm drag across as many as you are probably expecting to get results for then drag all those rows at once down. you can then enter different values inc2,c3,an so on.

as to how it works PFM!

[theboywhelp]

Whats PFM?

Thanks for the new formula. although I have copied and pasted it , its still not working.
I will keep working on it

[martindwilson]

PFM = pure *&*%$£%$ magic

[Cheeky Charlie]

Use a pivot table!

Post some data and I'll show you how powerful (quicker, faster, easier to understand) it can be.

HTH

[theboywhelp]

Thanks

This is the file I am trying to build.

Step 1
In sheet 1 i want to list all the batches for each sku code from sheet (330a)
and also the expirey date , status and quantity.

Step 2
Using the forecast (bw forecast) i want to be able to analyse by sku , whether the forecast is enough so that each batch will get used before it is too old , or whether I will have any left.

My ERP system should beable to do this , but it can't.

If you can get this working it would save me about 3 full days work a month.

Darren

[Cheeky Charlie]

Are you sure you've posted the right thing?

If you have... give us a chance, it doesn't say "SKU" anywhere, nor does it have a sheet(330a)

Please identify the base data and give an example of the ideal output.

We want to help, but I'm not here to spend hours deciphering your intent!

[theboywhelp]

Sorry cheeky charlie. I did post the wrong sheet.

Please see attached now.

In the OUTPUT sheet , i would like to get the 4 lines of info for each batch.Into the yellow cells.

Batch,Quantity,Expiry and Status these are all available on sheet 330a.
This I believe can be done by arrays , but cannot get them to work myself.

I then want to use the info in these cells to determine if all of the quantitys I have in each batch will be consumed by the forecast (BW sheet). If there is any remainder I would like to see that isn the red squares. We do not sell anything with less than 9 months expiry on it.

Hope this is enough info , and am genuinely grateful for the help I am getting here.

Darren

[Cheeky Charlie]

attached...?

I'm not too grumpy really, I just got a bit fed up wrestling with that workbook, now I understand why!

[theboywhelp]

oops.

here you go.

[shg]

Zipped and <100K.

[theboywhelp]

Third time lucky