help with a complicated VLOOKUP

gullit

New member
Joined
Aug 12, 2011
Messages
9
Reaction score
0
Points
0
Hi Gurus

I have a question about VLOOKUP formula and i really hope that you could help me with this.


___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.(result shown in cell D1) i got until this part but the trick is that since 1 product is in more than 1 location (it could be more than 2 locations) , i need the VLOOKUP to return the location with the lowest qty for that product, qty must be >0,

Any help with this problem wil be greatly appreciated.
 
Last edited:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Try this array formula

=INDEX(B:B,MIN(IF(($A$2:$A$20="X")*($C$2:$C$20>0),ROW($B$2:$B$20))))
 

gullit

New member
Joined
Aug 12, 2011
Messages
9
Reaction score
0
Points
0
Thank you Bob for your Help!
I tried the formula that you posted, it worked but i needed the result to be shown in a different cell. Looking around a found a formula that works just like i wanted. I have posted the formula below for anybody that is looking for somenthing similar!

=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))

Thanks a whole bunch for your help! this site rocks! I'll be back with more requests....lol!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Why didn't you put mine, which is more efficient, in that cell?
 
Top