Vlookup - Return All matches

[gizzylover]

I've tried searching the boards for Vlookup questions, but I'm not smart enough to figure out to modify previous postings to my current need - so appreciate your patience....

If I want to return ALL values found for a Vlookup (instead of first one found), how do I do that?

 

[Aladin Akyurek]

Hello guys,

Sorry to necro a thread but I'm having some real trouble with a vlookup duplicates. I'm very bad at Excel and I've been lumbered with something that is beyond my knowledge - hoping you can help.

I guess it is easiest to explain in full to avert any confusion...

I have three sheets right now:

Stock to pick
Data input
Data output

On "Stock to pick", we have two columns: "Stock" and "Quantity to pick"
On "Data input", we have "Stock", "Quantity" and "Location"
On "Data output", we have "Location", Stock, "Quantity in location" and "Quantity to pick".

My current formulas on "Data output" are as followed:

Location: =VLOOKUP('Stock To Pick'!A4,'Data Input'!A:C,3,FALSE)
Stock: =VLOOKUP('Stock To Pick'!A4,'Data Input'!A:C,1,FALSE)
Quantity in location: =VLOOKUP('Stock To Pick'!A4,'Data Input'!A:C,2,FALSE)
Quantity to pick: =VLOOKUP(B2,'Stock To Pick'!A:B,2,FALSE)

My problem arises because each unit of Stock in a varied location is listed individually, i.e.:

(Data input)
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Stock
[/TD]
[TD]Quantity
[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]London[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]Hamburg[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]London[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]Paris[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[TD]Paris[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[TD]Manchester[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]1[/TD]
[TD]London[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]1[/TD]
[TD]London[/TD]
[/TR]
</tbody>[/TABLE]












Of course, on my "Data output" sheet, the "Quantity to pick" field is a numeric value as opposed to having multiple lines of the same item of stock with a value of one (unlike the "Data input" sheet, which is multiple lines of quantity "1").

Right now, my Vlookup on "Data output" is giving me the first return of stock from the "Data input" sheet, where I may need two or three units of stock from two or three locations and I have no idea how to amend the formula to show multiple results without duplication!

I hope this is clear and any help would be greatly appreciated.

Many thanks,

MrBadAtExcel

You probably take up the right model. Looks more like a summarization problem (have a lokk at SumIf, etc.).

 

[RedJames]

I am having a similar issue, and am trying to apply this code, but seem to be having difficulty. I think it is because I am using different sheets and entire columns. I am trying to enter the formula in cell E3. I want it to look at what is in cell B3 and then pull all the records in column B on sheet 'DARTS offers' where B3 matches the data in column A of 'DARTS offers'. If anyone could help me, I would be most appreciative.

 

[RedJames]

I have tried this:

{=IF(ROWS(E$3:E4)<=D$3,INDEX('DARTS offers'!B:B,SMALL(IF('DARTS offers'!A:A =B$3,ROW('DARTS offers'!B:B)-ROW('DARTS offers'!B:B)+1),ROWS(E$3:E4))),"")}

It pulls the first one correctly, but when I drag it down the cells below get a number error.

 

[RedJames]

Sorry the formula above represents the formula in the first cell down that gets the number error, hence the (E$3:E4). The interesting thing is that on the next three cells below where I have dragged the formula, nothing at all apears. This is good as there are only two records for this lookup. I just need to figure out why I am getting this number error on the 2nd value.

 

[Domenic]

It looks like it should be...

D3:

=COUNTIF('DARTS Offers'!A:A,B3)

E3, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(E$3:E3)<=$D$3,INDEX('DARTS Offers'!B:B,SMALL(IF('DARTS Offers'!A:A=$B$3,ROW('DARTS Offers'!B:B)-MIN(ROW('DARTS Offers'!B:B))+1),ROWS(E$3:E3))),"")

Also, try to avoid whole column references, since it can slow down your worksheet. Instead, use dynamic named ranges, which adjust as data is added/removed.

Hope this helps!

 

[RedJames]

Thanks Domenic. I tried that, but now I am getting the #NUM error on all 4 of the below rows. It is still pulling the first return correctly though.

 

[Domenic]

Can you post a small sample of the data (about 10 rows or so), along with the value in B3?

 

[RedJames]

Sorry, I can't upload an example from work, but here is the data


B3= 1001234556

Column A

1001234523
1001234782
1001234987
1001234512
1001234556
1001234556
1001234586
1001234623
1001234676
1001234896

Column B


03/13/2013
04/26/2014
06/12/2014
05/14/2013
08/21/2013
06/24/2014
07/25/2014
12/12/2013
01/03/2013
06/14/2014

 

[Domenic]

It looks like there's nothing there out of the ordinary that exists. Just to make sure, did you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER? As your original post shows, curly braces {...} will automatically be placed around the formula by Excel.

 

[Domenic]

Also, make sure that the numbers are being recognized as numerical values, and not text values. So, for example, if A1 contains a number, the following returns TRUE...

=ISNUMBER(A1)

Are they true numerical values?