V-LOOKUP Lacking, need guidance

[Novellas]

[TABLE="width: 613"]
<TBODY>[TR]
[TD]Badge #</SPAN>[/TD]
[TD]Division</SPAN>[/TD]
[TD]Location Name</SPAN>[/TD]
[TD]Employee</SPAN>[/TD]
[TD]Product</SPAN>[/TD]
[TD]Count</SPAN>[/TD]
[TD]Date</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]14844</SPAN>[/TD]
[TD]403</SPAN>[/TD]
[TD]Denver 5</SPAN>[/TD]
[TD]Paul</SPAN>[/TD]
[TD]Apples</SPAN>[/TD]
[TD="align: right"]33</SPAN>[/TD]
[TD="align: right"]1/2/2012</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14844</SPAN>[/TD]
[TD]403</SPAN>[/TD]
[TD]Denver 5</SPAN>[/TD]
[TD]Paul </SPAN>[/TD]
[TD]Bananas</SPAN>[/TD]
[TD="align: right"]55</SPAN>[/TD]
[TD="align: right"]1/1/2012</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14844</SPAN>[/TD]
[TD]403</SPAN>[/TD]
[TD]Denver 7</SPAN>[/TD]
[TD]Paul</SPAN>[/TD]
[TD]Oranges</SPAN>[/TD]
[TD="align: right"]46</SPAN>[/TD]
[TD="align: right"]1/2/2012</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14846</SPAN>[/TD]
[TD]106</SPAN>[/TD]
[TD]Seattle 4 </SPAN>[/TD]
[TD]Phil</SPAN>[/TD]
[TD]Apples</SPAN>[/TD]
[TD="align: right"]34</SPAN>[/TD]
[TD="align: right"]1/3/2012</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14846</SPAN>[/TD]
[TD]106</SPAN>[/TD]
[TD]Seattle 4 </SPAN>[/TD]
[TD]Phil</SPAN>[/TD]
[TD]Bananas</SPAN>[/TD]
[TD="align: right"]34</SPAN>[/TD]
[TD="align: right"]1/3/2012</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14846</SPAN>[/TD]
[TD]106</SPAN>[/TD]
[TD]Seattle 5</SPAN>[/TD]
[TD]Phil</SPAN>[/TD]
[TD]Bananas</SPAN>[/TD]
[TD="align: right"]45</SPAN>[/TD]
[TD="align: right"]1/5/2012</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14850</SPAN>[/TD]
[TD]115</SPAN>[/TD]
[TD]Oakland 12</SPAN>[/TD]
[TD]Stan</SPAN>[/TD]
[TD]Oranges</SPAN>[/TD]
[TD="align: right"]42</SPAN>[/TD]
[TD="align: right"]1/5/2012</SPAN>[/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

This is the current formula I am using: =VLOOKUP($D25,'AUG INV'!$D$2:$U$2500,COLUMN($A1:B1),FALSE)

This Formula is sorting through the data sheet, selecting by Location Name & then populating the corresponding data via the "Column" formula, however if say there is another data set with the SAME Location Name but a different Product, the Formula doesn't distinguish and just pulls the SAME data set again & again.
I am looking for a Formula that will search the Inventory data, pulling by Location Name and pulling ALL instances with variable product (if applicable); will the INDEX formula work with this?

Appreciate all the expertise and assistance.

Thanks!

 

[jai9]

Hi,

I put your data in H1:N8 and put the locations in A11:A17.

Try this formula for the desired result.

=IFERROR(INDEX($N$1:$N$8, SMALL(IF(A11=$J$2:$J$8,ROW($J$2:$J$8)),COUNTIF($J$2:J2,J2)),0),"")

It is an array formula to be confirmed with CTRL+SHIFT+ENTER. Then drag down.

You might have to change ranges in the formula.

Jai

 

[Robert Mika]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Badge #	Division	Location Name	Employee	Product	Count	Date		Denver 5	2		Badge #	Division	Location Name	Employee	Product	Count	Date
2	14844	403	Denver 5	Paul	Apples	33	01/02/2012					14844	403	Denver 5	Paul	Apples	33	01/02/2012
3	14844	403	Denver 5	Paul	Bananas	55	01/01/2012					14844	403	Denver 5	Paul	Bananas	55	01/01/2012
4	14844	403	Denver 7	Paul	Oranges	46	01/02/2012
5	14846	106	Seattle 4	Phil	Apples	34	01/03/2012
6	14846	106	Seattle 4	Phil	Bananas	34	01/03/2012
7	14846	106	Seattle 5	Phil	Bananas	45	01/05/2012
8	14850	115	Oakland 12	Stan	Oranges	42	01/05/2012
Sheet3

Drag formula down and accross

 

[Novellas]

Robert & Jai9,

Thanks guys for your time & help! Appreciate the insights and tutelage! Problem solved!

-K

 

[Robert Mika]

Robert & Jai9,

Thanks guys for your time & help! Appreciate the insights and tutelage! Problem solved!

-K

You are welcome.

 

[Novellas]

bump

 

[Robert Mika]

bump

Here is an example:

https://dl.dropbox.com/u/78583609/Copy of vendors listing.xlsx

 

[Novellas]

Thank you Robert; you are a scholar & a gentleman.

 

[Robert Mika]

Thank you Robert; you are a scholar & a gentleman.

You are welcome.
Thanks for feedback