Row lookup based on a given value in the same row
 

Hello,

I am in the process of creating a quote book that pulls informatio
into one sheet that we give to our customer. With in this book ther
is a sheet where we enter in all of our vendor returns in the followin
colums (as a side note we have 10 vendors we get info from. So all
colums are multiplied out 10 times on the same row.):

Qte Qty 1 Price Qty 1 Qte Qty 2 Price Qty 2 MPQ LeadTime

I want to find the MPQ that goes with the minimum Price Qty 2.

I have used the Min function to locate the minimum Price Qty 2. I a
trying to locate the MPQ based on the min price QTY for each item.

I am not sure how else to explain this. If there any questions let m
know.

Thanks in advance.

Dan Dodso

--
Message posted from http://www.ExcelForum.com

Row lookup based on a given value in the same row
 

Hi
try
=VLOOKUP(MIN(D1:D100),D1:E100,2,0)

--
Regards
Frank Kabel
Frankfurt, Germany


Hello,

I am in the process of creating a quote book that pulls information
into one sheet that we give to our customer. With in this book there
is a sheet where we enter in all of our vendor returns in the
following colums (as a side note we have 10 vendors we get info from.
So all 6 colums are multiplied out 10 times on the same row.):

Qte Qty 1 Price Qty 1 Qte Qty 2 Price Qty 2 MPQ LeadTime

I want to find the MPQ that goes with the minimum Price Qty 2.

I have used the Min function to locate the minimum Price Qty 2. I am
trying to locate the MPQ based on the min price QTY for each item.

I am not sure how else to explain this. If there any questions let me
know.

Thanks in advance.

Dan Dodson


---
Message posted from http://www.ExcelForum.com/

Row lookup based on a given value in the same row
 

Thanks for the suggestion Frank. However it did not work.

The following is the formula I used based on the number of vendors.

=VLOOKUP(MIN(Q3,X3,AE3,AL3,AS3,AZ3,BG3,BN3,M3),(Q3 :R100,X3:Y100,AE3:AF100,AL3:AM100,AS3:AT100,AA3:AZ 100,BG3:BH100,BN3:BO100,M3:N100),2,0)

Thanks for any other suggestions.

Da

--
Message posted from http://www.ExcelForum.com

Row lookup based on a given value in the same row
 

Dan,

Here is one approach to getting your answer. You need to create a summary
block of the vendor information. Then you can get the minimum value in the
summary block. ColI has the vendor names, ColJ has the minimum "Price Qty 2"
value for the given vendor . ColK has the corresponding MPQ value for the
given vendor.

Cell J7 will have the overall minimum "Price Qty 2" value for all 10
vendors.
Cell K7 will have your final answer (if I've understood your question
properly).

Troy

----- Summary Block of 10 Vendors:
I10: Vendors
I11: Vendor1
I12: Vendor2
I13: Vendor3
....
I20: Vendor10

J10: Col4
J11: =MIN(Q11:Q19)
J12: =MIN(X11:X19)
J13: =MIN(AE11:AE19)
....
J20: =MIN(...)

K10: Col5
K11: =INDEX(R11:R19,MATCH(MIN(Q11:Q19),Q11:Q19,0))
K12: =INDEX(Y11:Y19,MATCH(MIN(X11:X19),X11:X19,0))
K13: =INDEX(AF11:AF19,MATCH(MIN(AE11:AE19),AE11:AE19,0) )
....
K20: =INDEX(...))

------ Overall Results:
J7: =MIN(J11:J20)
K7: =INDEX(K11:K20,MATCH(MIN(J11:J20),J11:J20,0))


"Danno " wrote in message
...
Thanks for the suggestion Frank. However it did not work.

The following is the formula I used based on the number of vendors.


=VLOOKUP(MIN(Q3,X3,AE3,AL3,AS3,AZ3,BG3,BN3,M3),(Q3 :R100,X3:Y100,AE3:AF100,AL
3:AM100,AS3:AT100,AA3:AZ100,BG3:BH100,BN3:BO100,M3 :N100),2,0)

Thanks for any other suggestions.

Dan


---
Message posted from http://www.ExcelForum.com/