Forum Discussion
DanWebtronx
Sep 06, 2022Copper Contributor
CELL Function with a MIN formula as a reference
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 t...
DanWebtronx
Sep 06, 2022Copper Contributor
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.
=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.
Detlef_Lewin
Sep 06, 2022Silver Contributor
Your model needs a rework.
It is probably some kind of crosstabular table.
But you need a record list.
- DanWebtronxSep 06, 2022Copper ContributorA record list?
- Detlef_LewinSep 06, 2022Silver Contributor
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.
- mathetesSep 06, 2022Silver Contributor
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.
- DanWebtronxSep 06, 2022Copper ContributorOkay, 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.