complicated VLOOKUP formula

[gullit]

Hi
I have a question about VLOOKUP formula

___A_______ B_______ C_______

__prod_____ loc______ qty______
_____________________________
1___X_______C1_______20_________
2___________ B1 _______15________
3___L________D1________100______
4 ___________E1________ 35_______
5___R________F1________17______
6 ___________G1 ________97______

What I need is a VLOOKUP formula where my LOOKUP value is "prod" and it should return the location of the product. i got untill this part but the trick is that since 1 product is in different locations , i need the VLOOKUP to return the location with the lowest qty for that product, qty must be >0, if 0 in location return value must be "0 in stock" or somenthing like that.

Any help with this problem wil be greatly appreciated.

 

[gullit]

Hi T. Valko

Lets say that I filled all the blank cells. I have follow the link and i don't see how that will help with the solution?
i'm new in excel and don't really want to use a BVA code into the sheet, unless tha's the only option.

 

[T. Valko]

Hi T. Valko

Lets say that I filled all the blank cells. I have follow the link and i don't see how that will help with the solution?
i'm new in excel and don't really want to use a BVA code into the sheet, unless tha's the only option.

If you fill in the empty product cells:

Book1
	A	B	C	D	E	F
1	prod	loc	qty	_	prod	qty
2	X	C1	20	_	L	35
3	X	B1	15	_	_	_
4	L	D1	100	_	_	_
5	L	E1	35	_	_	_
6	R	F1	17	_	_	_
7	R	G1	97	_	_	_
Sheet1

Then this array formula** entered in F2:

=MIN(IF(A2:A7=E2,C2:C7))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Marcelo Branco]

T. Valko is right, filling the blanks would make the formulas much simpler, but my solution in #9 works. Have you tried it?

M.

 

[gullit]

T. Valko, i have entered the array formula and it works, it does gives me the qty of the location. but what should i do to count only cells that the qty is >0 and i will need the location with the fewest qty to show in the spread sheet instead of the qty?

Don't want to be a pain in the butt with so many requests. You guys are helping me a whole bunch nad i really appreciate the help!

 

[gullit]

Hi Danny

I have tried the formula, the first part of the formula works.
When i try the second part of the formula, the result that i get is "prod" and not the location.

I really like to try the 2 solutions recomended (ypur's and T. Valko), because i know it will help me in the future and will expand my knowledge of excel a lot.

 

[T. Valko]

T. Valko, i have entered the array formula and it works, it does gives me the qty of the location. but what should i do to count only cells that the qty is >0 and i will need the location with the fewest qty to show in the spread sheet instead of the qty?

Don't want to be a pain in the butt with so many requests. You guys are helping me a whole bunch nad i really appreciate the help!

Like this...

Book1
	A	B	C	D	E	F	G
1	prod	loc	qty	_	prod	loc	qty
2	X	C1	20	_	L	E1	35
3	X	B1	15	_	_	_	_
4	L	D1	0	_	_	_	_
5	L	E1	35	_	_	_	_
6	R	F1	17	_	_	_	_
7	R	G1	97	_	_	_	_
Sheet1

This array formula** entered in F2 for the location:

=INDEX(B2:B7,MATCH(1,IF(A2:A7=E2,IF(C2:C7=MIN(IF(A2:A7=E2,IF(C2:C7>0,C2:C7))),1)),0))

This array formula** entered in G2 for the quantity:

=MIN(IF(A2:A7=E2,IF(C2:C7>0,C2:C7)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[gullit]

Like this...

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Verdana,Arial; FONT-SIZE: 10pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"><COL style="WIDTH: 37px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">prod</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">loc</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">qty</TD><TD style="BORDER-BOTTOM: #000000 1px solid; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">prod</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">loc</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">qty</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">X</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">C1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">20</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">L</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">E1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">35</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">X</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">B1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">15</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">L</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">D1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">0</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">L</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">E1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">35</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">R</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">F1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">17</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">R</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">G1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">97</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR></TBODY></TABLE>


This array formula** entered in F2 for the location:

=INDEX(B2:B7,MATCH(1,IF(A2:A7=E2,IF(C2:C7=MIN(IF(A2:A7=E2,IF(C2:C7>0,C2:C7))),1)),0))

This array formula** entered in G2 for the quantity:

=MIN(IF(A2:A7=E2,IF(C2:C7>0,C2:C7)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

It works like magic!! , thank you everybody for you help..This forum is the best!

 

[T. Valko]

It works like magic!! , thank you everybody for you help..This forum is the best!

You're welcome. Thanks for the feedback!