CELL Function with a MIN formula as a reference

[DanWebtronx]

Is there a way to find the cell that a MIN formula is referencing? Tried using the CELL function's "address" info type, but it results in an error. The MIN formula I'm using is huge, and I'm trying to find which cell it's referencing so I can use that cell to index other info. Any help on the subject would be greatly appreciated.

[#2]

@DanWebtronx 

=INDEX($C$2:$C$21,MATCH(MIN($B$2:$B$21),$B$2:$B$21,0))

If you want the cell with the smallest value index other info you can try INDEX and MATCH.

 

[#3]

I wish it were that simple. The values occur more than once, and on multiple columns. The MIN formula I'm using is finding the lowest cost for a particular items, pulling from multiple arrays. The function I'm using is:

=MIN(IFERROR(INDEX('Vendor Data'!D:D,MATCH(D2,'Vendor Data'!B:B,0)),IFERROR(INDEX('Vendor Data'!D:D,MATCH(E2,'Vendor Data'!C:C,0)),99999)),IFERROR(INDEX('Vendor Data'!J:J,MATCH(D2,'Vendor Data'!H:H,0)),IFERROR(INDEX('Vendor Data'!J:J,MATCH(E2,'Vendor Data'!I:I,0)),99999)),IFERROR(INDEX('Vendor Data'!P:P,MATCH(D2,'Vendor Data'!N:N,0)),IFERROR(INDEX('Vendor Data'!P:P,MATCH(E2,'Vendor Data'!O:O,0)),99999)),IFERROR(INDEX('Vendor Data'!V:V,MATCH(D2,'Vendor Data'!T:T,0)),IFERROR(INDEX('Vendor Data'!V:V,MATCH(E2,'Vendor Data'!U:U,0)),99999)),IFERROR(INDEX('Vendor Data'!AB:AB,MATCH(D2,'Vendor Data'!Z:Z,0)),IFERROR(INDEX('Vendor Data'!AB:AB,MATCH(E2,'Vendor Data'!AA:AA,0)),99999)),IFERROR(INDEX('Vendor Data'!AH:AH,MATCH(D2,'Vendor Data'!AF:AF,0)),IFERROR(INDEX('Vendor Data'!AH:AH,MATCH(E2,'Vendor Data'!AG:AG,0)),99999)),IFERROR(INDEX('Vendor Data'!AN:AN,MATCH(D2,'Vendor Data'!AL:AL,0)),IFERROR(INDEX('Vendor Data'!AN:AN,MATCH(E2,'Vendor Data'!AM:AM,0)),99999)),IFERROR(INDEX('Vendor Data'!AT:AT,MATCH(D2,'Vendor Data'!AR:AR,0)),IFERROR(INDEX('Vendor Data'!AT:AT,MATCH(E2,'Vendor Data'!AS:AS,0)),99999))*IFERROR(INDEX('Vendor Data'!AZ:AZ,MATCH(D2,'Vendor Data'!AX:AX,0)),IFERROR(INDEX('Vendor Data'!AZ:AZ,MATCH(E2,'Vendor Data'!AY:AY,0)),99999)),IFERROR(INDEX('Vendor Data'!BF:BF,MATCH(D2,'Vendor Data'!BD:BD,0)),IFERROR(INDEX('Vendor Data'!BF:BF,MATCH(E2,'Vendor Data'!BC:BC,0)),99999)),IFERROR(INDEX('Vendor Data'!BL:BL,MATCH(D2,'Vendor Data'!BJ:BJ,0)),IFERROR(INDEX('Vendor Data'!BL:BL,MATCH(E2,'Vendor Data'!BK:BK,0)),99999)),IFERROR(INDEX('Vendor Data'!BR:BR,MATCH(D2,'Vendor Data'!BP:BP,0)),IFERROR(INDEX('Vendor Data'!BR:BR,MATCH(E2,'Vendor Data'!BQ:BQ,0)),99999)),IFERROR(INDEX('Vendor Data'!BX:BX,MATCH(D2,'Vendor Data'!BU:BU,0)),IFERROR(INDEX('Vendor Data'!BX:BX,MATCH(E2,'Vendor Data'!BV:BV,0)),99999)),IFERROR(INDEX('Vendor Data'!CD:CD,MATCH(D2,'Vendor Data'!CB:CB,0)),IFERROR(INDEX('Vendor Data'!CD:CD,MATCH(E2,'Vendor Data'!CC:CC,0)),99999)),IFERROR(INDEX('Vendor Data'!CJ:CJ,MATCH(D2,'Vendor Data'!CH:CH,0)),IFERROR(INDEX('Vendor Data'!CJ:CJ,MATCH(E2,'Vendor Data'!CI:CI,0)),99999)),IFERROR(INDEX('Vendor Data'!CP:CP,MATCH(D2,'Vendor Data'!CN:CN,0)),IFERROR(INDEX('Vendor Data'!CP:CP,MATCH(E2,'Vendor Data'!CO:CO,0)),99999)),IFERROR(INDEX('Vendor Data'!CV:CV,MATCH(D2,'Vendor Data'!CT:CT,0)),IFERROR(INDEX('Vendor Data'!CV:CV,MATCH(E2,'Vendor Data'!CU:CU,0)),99999)),IFERROR(INDEX('Vendor Data'!DB:DB,MATCH(D2,'Vendor Data'!CZ:CZ,0)),IFERROR(INDEX('Vendor Data'!DB:DB,MATCH(E2,'Vendor Data'!DA:DA,0)),99999)),IFERROR(INDEX('Vendor Data'!DH:DH,MATCH(D2,'Vendor Data'!DF:DF,0)),IFERROR(INDEX('Vendor Data'!DH:DH,MATCH(E2,'Vendor Data'!DG:DG,0)),99999)),IFERROR(INDEX('Vendor Data'!DN:DN,MATCH(D2,'Vendor Data'!DL:DL,0)),IFERROR(INDEX('Vendor Data'!DN:DN,MATCH(E2,'Vendor Data'!DM:DM,0)),99999)),IFERROR(INDEX('Vendor Data'!DT:DT,MATCH(D2,'Vendor Data'!DR:DR,0)),IFERROR(INDEX('Vendor Data'!DT:DT,MATCH(E2,'Vendor Data'!DS:DS,0)),99999)),IFERROR(INDEX('Vendor Data'!DZ:DZ,MATCH(D2,'Vendor Data'!DX:DX,0)),IFERROR(INDEX('Vendor Data'!DZ:DZ,MATCH(E2,'Vendor Data'!DY:DY,0)),99999)),IFERROR(INDEX('Vendor Data'!EF:EF,MATCH(D2,'Vendor Data'!ED:ED,0)),IFERROR(INDEX('Vendor Data'!EF:EF,MATCH(E2,'Vendor Data'!EE:EE,0)),99999)),IFERROR(INDEX('Vendor Data'!EL:EL,MATCH(D2,'Vendor Data'!EJ:EJ,0)),IFERROR(INDEX('Vendor Data'!EL:EL,MATCH(E2,'Vendor Data'!EK:EK,0)),99999)),IFERROR(INDEX('Vendor Data'!ER:ER,MATCH(D2,'Vendor Data'!EP:EP,0)),IFERROR(INDEX('Vendor Data'!ER:ER,MATCH(E2,'Vendor Data'!EQ:EQ,0)),99999)),IFERROR(INDEX('Vendor Data'!EX:EX,MATCH(D2,'Vendor Data'!EU:EU,0)),IFERROR(INDEX('Vendor Data'!EX:EX,MATCH(E2,'Vendor Data'!EV:EV,0)),99999)),IFERROR(INDEX('Vendor Data'!FD:FD,MATCH(D2,'Vendor Data'!FB:FB,0)),IFERROR(INDEX('Vendor Data'!FD:FD,MATCH(E2,'Vendor Data'!FC:FC,0)),99999)),IFERROR(INDEX('Vendor Data'!FJ:FJ,MATCH(D2,'Vendor Data'!FH:FH,0)),IFERROR(INDEX('Vendor Data'!FJ:FJ,MATCH(E2,'Vendor Data'!FI:FI,0)),99999)),IFERROR(INDEX('Vendor Data'!FP:FP,MATCH(D2,'Vendor Data'!FN:FN,0)),IFERROR(INDEX('Vendor Data'!FP:FP,MATCH(E2,'Vendor Data'!FO:FO,0)),99999)),IFERROR(INDEX('Vendor Data'!FV:FV,MATCH(D2,'Vendor Data'!FT:FT,0)),IFERROR(INDEX('Vendor Data'!FV:FV,MATCH(E2,'Vendor Data'!FU:FU,0)),99999)))

It does work, but I'd like to know what cell it's referencing, so I can index it to see which vendor that cost is being pulled from.

[#4]

@DanWebtronx 

From what I read about CELL, it won't do what you want to do. However, assuming your MIN is referring to a range of cells (either a column or a row), something like this can work to find--in this case--the row within the designated array.

=MATCH(MIN(F7:F11),F7:F11,0)

And with that you could index other things from the same row in a two-dimensional array. (Assumption: only one cell in the full array contains that MIN value....

[#5]

Unfortunately, that assumption is incorrect, and the MIN value occurs more than once. Thanks for the suggestion though.

[#6]

@DanWebtronx 

Yes...my first reply was clearly crossing the exchange you had with another similar reply. Reading your MIN formula--you said it was "huge" but that was an understatement!!!!!!!!--makes me wonder how the data are arrayed in the first place, and whether or not there's a way to reorganize it.

That aside, are you familiar with the relatively new FILTER function? Without knowing how your data are arrayed, this may totally miss the mark, but perhaps FILTER, in conjunction with the MIN function could at least find the row(s) that contain that MIN value.

Here's a link to a good resource of FILTER.

[#7]

@DanWebtronx 

Your model needs a rework.

It is probably some kind of crosstabular table.

But you need a record list.

[#8]

It's organized in a way that one could paste up to 30 vendor lists into one sheet. Each one is separated so we can distinguish which vendor the list is from. I'm just surprised there isn't an obvious way to reference the data of a MIN formula beside matching.

I can't figure out the FILTER function would give me the row or column the minimum value is found.

[#9]

A record list?

[#10]

@DanWebtronx 

There is a pattern in your formula and it should be possible to simplify the formula and return the cell reference with a formula. However i'm unsure if this can be done without seeing the sheet.

Without a formula or as a makeshift solution you can use evaluate formula which shows the cell reference. This would be a manual solution which can be done in little time.

[#11]

@DanWebtronx 

A record list = a single set of records, one per entity per product (or whatever makes sense). A well defined table. Consistent use of data types within each column, no blanks between rows....that kind of stuff.

[#12]

Okay, thanks for the ideas guys. I will look into what can be done with the data sets and see if the formula can be simplified.

[#13]

@DanWebtronx 

Paste your 30 vendor lists in continuous columns and add an extra column for the vendor name.

Now it will be easy to determine the min value and the corresponding vendor.