• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index MIN Row (Retrieving min cost -multiple similar instances)

brian sullivan

brian sullivan

Member
I'm trying to use a Index Min Row (Array Formula) with a data set that has multiple instances /same location/shipment size. However, this function is only pulling in the first instance as opposed to the min scenario. The reason I'm utilizing this formula is because (my real project) is using data files with different text strings rejecting vlookups.




Notes in Excel file----
Formula in Cell
‘{=INDEX('2014 data'!L:L,MIN(IF(('2014 data'!G:G='2015 data'!C7)*('2014 data'!H:H='2015 data'!E7)*('2014 data'!I:I='2015 data'!F7)*('2014 data'!J:J='2015 data'!G7),ROW('2014 data'!L:L))))}’
Result
Pulling in first instance of data meeting lookup conditions (yellow) when utilizing formula within array context(ctrl /shift/enter
Desired Result
I want to pull in the minimum cost factor in the 2014 data tab using this formula structure. I want to pull in ten. The original data files I'm using (not in here) have data from different excel version that don't like v-lookups (even if you use trim on cells).
 

Attachments

try array-entering:
=MIN(IF(ISNUMBER('2014 data'!$L$4:$L$10),('2014 data'!$L$4:$L$10)*('2014 data'!$H$4:$H$10=E7)*('2014 data'!$I$4:$I$10=F7)*('2014 data'!$J$4:$J$10=G7)))

I've not used entire columns as they can take a very long time to calculate.

I suspect a pivot table could give you what you want with less fuss.
 
Last edited:
=MIN(IF(ISNUMBER('2014 data'!L4:L10),('2014 data'!L4:L10)*('2014 data'!G4:G10=C7)*('2014 data'!$H$4:$H$10=E7)*('2014 data'!$I$4:$I$10=F7)*('2014 data'!$J$4:$J$10=G7)))

The formula retrieves data amazing well when all the text fields, but what's the solution for overcoming data when any field is different ....
 
Formula in Cell
Formula in Cell I 8 retrieves a zero because on the 2014 data sheet there's a location country (that's not spain)
Desired Result
Value of 10 in cell. What is the formula that will retreive 10 even if there are countries with different data
 
Try (array-entered):
=MIN(IFERROR(('2014 data'!$L$4:$L$10)*IF('2014 data'!$G$4:$G$10=C8,1,NA())*IF('2014 data'!$H$4:$H$10=E8,1,NA())*IF('2014 data'!$I$4:$I$10=F8,1,NA())*IF('2014 data'!$J$4:$J$10=G8,1,NA()),FALSE))

Poduces a 0 in I4 as Venice isn't present on the 2014 data sheet, so there is no price. Difficult to distinguish this from a true 0 cost.

Are you sure a pivot table couldn't serve you better?
I've put a simple one in the attached file on the 2015 Data sheet wich shows the min cost of each of the existing combinations. Some tweaks to it to give you exactly the info you need by adding a column or two and further by filtering on any field. It took about 1 minute to create.
 

Attachments

You must log in or register to reply here.
Back
Top