Excel/Match smallest and largest
Im currently trying to use the match and if functions to very little success. Basically the match function hinges on a post code input in cell Sheet2 C5, I then have multiple index match cells on this sheet which will bring up relevant info matching that post code (EG address town etc) It will 1st check if the post code is one of our agents (In the BR and RRC sheet) if there is no entry on here it will then check the achieve sheet (Where the problem arrises). In incidents where there is more than 1 post code entry I need to get the maximum and minimum amounts (In column 11) attached to this post code
=IF(COUNTIF('BR and RRC'!F1:F624,Sheet2!C5),INDEX('BR and RRC'!A1:F624,MATCH(Sheet2!C5,'BR and RRC'!F1:F624,0),1),INDEX(Achieve!A2:AB62638,MATCH(Sheet2!C5,Achieve!B1:B62638,0),11))
This is what I have so far, it works fine for the address in other columns but I want it to retrive the highest (and lowest in another cell) cost linked with the inputted post code while still retaining the if function. Hope that makes sense
You can get values that meet your condition by starting with this
this would return and array with False for any rows that did not match in column B and the number from column K for those that do.
then you could use
for the minimum value and
These would be array formulas and entered with Ctrl+Shift+Enter. You can use this same methodology/approach to rebuild the INDEX-MATCH sections in your IF formula and then the whole formula would be array entered (Ctrl+Shift+Enter).
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here