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...
OliverScheurich
Sep 06, 2022Gold Contributor
=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.
- DanWebtronxSep 06, 2022Copper ContributorI 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.- Detlef_LewinSep 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?
- OliverScheurichSep 06, 2022Gold Contributor
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.