Index match Function

%3CLINGO-SUB%20id%3D%22lingo-sub-2379748%22%20slang%3D%22en-US%22%3EIndex%20match%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2379748%22%20slang%3D%22en-US%22%3E%3CP%3Eplease%20help%20me%20out%2C%20i%20need%20a%20formula%20to%20select%20column%20name%2C%20on%20basis%20of%20finding%20the%20min%20value%20.%3C%2FP%3E%3CP%3Ei%20need%20a%20formula%20to%20selection-1%20and%20selction-2%20for%20selecting%20vendor%20with%20min%20values%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2257px%22%3Eplace%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2257px%22%3Evendor-1%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2257px%22%3Evendor-2%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2257px%22%3Evendor-3%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%20height%3D%2257px%22%3Evendor-4%3C%2FTD%3E%3CTD%20width%3D%225%25%22%20height%3D%2257px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3Eselection-1%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%225%25%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3Eselection-2%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EUSA%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E5000%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E7000%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E5000%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%20height%3D%2230px%22%3E8000%3C%2FTD%3E%3CTD%20width%3D%225%25%22%20height%3D%2230px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3Evendor-1%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%225%25%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3Evendor-3%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EIndia%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E9000%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E8000%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E8000%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%20height%3D%2230px%22%3E9500%3C%2FTD%3E%3CTD%20width%3D%225%25%22%20height%3D%2230px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3Evendor-2%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%225%25%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3Evendor-3%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2379748%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2379778%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20match%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2379778%22%20slang%3D%22en-US%22%3E%3CP%3E*%20What%20happens%20if%20there%20are%203%20or%20more%20vendors%20that%20match%20the%20minimum%20value%3F%3CBR%20%2F%3E*%20You%20will%20be%20much%20better%20off%20if%20you%20can%20reorganize%20the%20data%20to%20the%20format%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3Eplace%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3Evendor%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3Evalue%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3Eusa%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3Evendor-1%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E4000%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3Eusa%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3Evendor-2%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E2000%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eetc.%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20can%20do%20that%2C%20there%20are%20fairly%20straightforward%20approaches%20to%20consider.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061211%22%20target%3D%22_blank%22%3E%40swstr510%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2379946%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20match%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2379946%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061211%22%20target%3D%22_blank%22%3E%40swstr510%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20wish%20to%20retain%20the%20crosstab%20layout%20and%20have%20Excel%20365%20you%20can%20return%20a%20sorted%20list%20of%20vendors%20using%20the%20SORTBY%20function%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20valuesRanked%2C%20SORTBY(Values%5B%23Headers%5D%2CValues%5B%40%5D)%2C%0A%20%20INDEX(valuesRanked%2C%20%7B1%2C2%7D))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EWith%20older%20versions%20of%20Excel%20one%20is%20faced%20with%20ranking%20the%20values%2C%20using%20COUNTIFS%20as%20a%20tie-breaker%2C%20and%20then%20using%20MATCH%2FINDEX%20to%20lookup%20the%20header.%3C%2FP%3E%3CP%3ENote.%20I%20paced%20the%20value%20columns%20in%20a%20Table%20in%20order%20to%20provide%20structured%20references%20to%20the%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2380585%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20match%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380585%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20have%20attached%20a%20file%2C%20my%20problem%20is%20when%20two%20vendors%20have%20same%20rate%20then%20it%20should%20come%20in%20O%20column%2C%20like%20in%20Australia%20rate%20are%2013000%2C%20but%20of%20different%20vendors%20but%20in%20my%20formula%20same%20Logistics%20vendor%20-3%20occurs%2C%20i%20want%20in%20O%20column%20Logistics%20vendor%205%20should%20come.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2381666%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20match%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2381666%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061211%22%20target%3D%22_blank%22%3E%40swstr510%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20prefer%20the%20simplicity%20of%20the%20365%20array%20solution%20but%20that%20is%20only%20any%20good%20if%20you%20have%20access%20to%20the%20app.%3C%2FP%3E%3CP%3EOtherwise%20you%20should%20be%20able%20to%20use%20something%20like%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20INDEX(%20%0A%20%20%20%20availableRates%2C%20%0A%20%20%20%20MATCH(%201%2C%20%0A%20%20%20%20%20%201%20%2B%20COUNTIFS(availableRates%2C%22%26lt%3B%22%26amp%3BavailableRates)%20%0A%20%20%20%20%20%20%20%20%2B%20COUNTIFS(availableRates%2CavailableRates%2C%20vendor%2C%22%26lt%3B%22%26amp%3Bvendor)%2C%0A%20%20%20%200)%20%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2382694%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20match%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2382694%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eeverything%20is%20ok%20in%20your%20normal%20excel%20formula%2C%20but%20problem%20occurs%20if%20any%20vendor%20give%20no%26nbsp%3B%20rates%20or%200%20then%20they%20should%20not%20be%20counted%2C%20like%20i%20highlighted%20in%20yellowed%20color%20.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2382872%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20match%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2382872%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061211%22%20target%3D%22_blank%22%3E%40swstr510%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20only%20develop%20for%20365%20these%20days%2C%20having%20disposed%20of%20earlier%20versions%20of%20Excel%20as%20unfit%20for%20purpose!%26nbsp%3B%20However%2C%20casting%20my%20mind%20back%20to%20how%20I%20used%20to%20do%20these%20things%2C%20it%20would%20be%20relatively%20easy%20to%20test%20for%20zero%20rates.%26nbsp%3B%20At%20present%2C%20I%20interpret%20a%20zero%20rate%20as%20an%20offer%20to%20carry%20the%20goods%20without%20charge%20(as%20opposed%20to%20a%20blank%20field%20or%20any%20text%20field%20that%20would%20lead%20to%20the%20vendor%20being%20eliminated%20from%20consideration).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20reverse%20this%20it%20would%20be%20necessary%20to%20add%20a%20specific%20test%20for%20zero%20rates%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%20INDEX(%20availableRates%2C%20%0A%20%20%20%20MATCH(1%2C%20%0A%20%20%20%20%20%201%20%2B%20COUNTIFS(availableRates%2C%20%22%26lt%3B%22%26amp%3BavailableRates%2C%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20availableRates%2C%20%22%26gt%3B0%22)%20%0A%20%20%20%20%20%20%20%20%2B%20COUNTIFS(availableRates%2C%20availableRates%2C%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20vendor%2C%20%22%26lt%3B%22%26amp%3Bvendor)%2C%20%0A%20%20%20%200)%20%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20365%2C%20this%20requires%20a%20FILTER%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20%20sortedRates%2C%20%20%20SORTBY(availableRates%2CavailableRates)%2C%0A%20%20%20%20sortedVendors%2C%20SORTBY(vendor%2C%20availableRates)%2C%0A%20%20%20%20stacked%2C%20IF(%7B1%3B0%7D%2C%20sortedRates%2C%20sortedVendors)%2C%0A%20%20%20%20filtered%2C%20FILTER(stacked%2C%20sortedRates%26gt%3B0)%2C%0A%20%20%20%20INDEX(filtered%2C%20%7B1%2C2%2C1%2C2%7D%2C%20%7B1%2C1%2C2%2C2%7D)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2385018%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20match%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385018%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20applied%20the%20same%20logic%20but%20didn't%20work%2C%20as%20check%20the%20attached%20sheet%2C%20where%20AG%20column%20highlighted%20with%20red%20color%20are%20the%20error%20as%20no%20rate%20comes%20in%20L-1%20.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2386013%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20match%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2386013%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20have%20attached%20a%20file%2C%20i%20need%20min%20rates%20L-1%2CL-2%2CL-3%2C%20L-4%20RATES%2C%20FROM%20VENDORS%20RATES%20G180%3AAF180%20ROWS%20RANGE%2C%20AND%20ON%20BASIS%20OF%20MIN%20RATE%2C%20VENDORS%20NAME%20SHOULD%20BE%20REQUIRED%20.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2386940%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20match%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2386940%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22user-mention-tag%22%3E%3CP%20class%3D%22user-mention-tag-label%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22user-mention-tag-label%22%3E%3CSPAN%3E%26nbsp%3Bhave%20attached%20a%20file%2C%20i%20need%20min%20rates%20L-1%2CL-2%2CL-3%2C%20L-4%20RATES%2C%20FROM%20VENDORS%20RATES%20G180%3AAF180%20ROWS%20RANGE%2C%20AND%20ON%20BASIS%20OF%20MIN%20RATE%2C%20VENDORS%20NAME%20SHOULD%20BE%20REQUIRED%20.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

please help me out, i need a formula to select column name, on basis of finding the min value .

i need a formula to selection-1 and selction-2 for selecting vendor with min values

placevendor-1vendor-2vendor-3vendor-4selection-1selection-2
USA5000700050008000vendor-1vendor-3
India9000800080009500vendor-2vendor-3
10 Replies

* What happens if there are 3 or more vendors that match the minimum value?
* You will be much better off if you can reorganize the data to the format

placevendorvalue
usavendor-14000
usavendor-22000
etc.  

 

If you can do that, there are fairly straightforward approaches to consider.

@swstr510 

@swstr510 

If you wish to retain the crosstab layout and have Excel 365 you can return a sorted list of vendors using the SORTBY function

= LET(
  valuesRanked, SORTBY(Values[#Headers],Values[@]),
  INDEX(valuesRanked, {1,2}))

With older versions of Excel one is faced with ranking the values, using COUNTIFS as a tie-breaker, and then using MATCH/INDEX to lookup the header.

Note. I paced the value columns in a Table in order to provide structured references to the data.

@Peter Bartholomew 

i have attached a file, my problem is when two vendors have same rate then it should come in O column, like in Australia rate are 13000, but of different vendors but in my formula same Logistics vendor -3 occurs, i want in O column Logistics vendor 5 should come.

@swstr510 

I prefer the simplicity of the 365 array solution but that is only any good if you have access to the app.

Otherwise you should be able to use something like

= INDEX( 
    availableRates, 
    MATCH( 1, 
      1 + COUNTIFS(availableRates,"<"&availableRates) 
        + COUNTIFS(availableRates,availableRates, vendor,"<"&vendor),
    0) 
  )

@Peter Bartholomew 

everything is ok in your normal excel formula, but problem occurs if any vendor give no  rates or 0 then they should not be counted, like i highlighted in yellowed color .

@swstr510 

I only develop for 365 these days, having disposed of earlier versions of Excel as unfit for purpose!  However, casting my mind back to how I used to do these things, it would be relatively easy to test for zero rates.  At present, I interpret a zero rate as an offer to carry the goods without charge (as opposed to a blank field or any text field that would lead to the vendor being eliminated from consideration).

 

To reverse this it would be necessary to add a specific test for zero rates

 

= INDEX( availableRates, 
    MATCH(1, 
      1 + COUNTIFS(availableRates, "<"&availableRates, 
                   availableRates, ">0") 
        + COUNTIFS(availableRates, availableRates, 
                   vendor, "<"&vendor), 
    0) 
  )

 

In 365, this requires a FILTER

 

= LET(
    sortedRates,   SORTBY(availableRates,availableRates),
    sortedVendors, SORTBY(vendor, availableRates),
    stacked, IF({1;0}, sortedRates, sortedVendors),
    filtered, FILTER(stacked, sortedRates>0),
    INDEX(filtered, {1,2,1,2}, {1,1,2,2})
  )

 

@Peter Bartholomew 

I applied the same logic but didn't work, as check the attached sheet, where AG column highlighted with red color are the error as no rate comes in L-1 .

@Hans Vogelaar @Peter Bartholomew 

 

i have attached a file, i need min rates L-1,L-2,L-3, L-4 RATES, FROM VENDORS RATES G180:AF180 ROWS RANGE, AND ON BASIS OF MIN RATE, VENDORS NAME SHOULD BE REQUIRED .

@Hans Vogelaar @Peter Bartholomew

 have attached a file, i need min rates L-1,L-2,L-3, L-4 RATES, FROM VENDORS RATES G180:AF180 ROWS RANGE, AND ON BASIS OF MIN RATE, VENDORS NAME SHOULD BE REQUIRED .

@swstr510 

The text fields weren't ranked as I expected.  The attached contains a test to eliminate text fields from consideration and may behave a little better.  

 

Basically I switched to Excel 365 to avoid traditional spreadsheet formulas like this so I am probably not the best person to perfect the formulas.