You are here:

# Excel/Match smallest and largest

Question
Hi Tom

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

Kind Regards

Ben

Ben,

You can get values that meet your condition by starting with this

=if(Achieve!\$B\$2:\$B\$62638=Sheet2!C5,Achieve!\$K\$2:\$K\$62638)

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

=Small(if(Achieve!\$B\$2:\$B\$62638=Sheet2!C5,Achieve!\$K\$2:\$K\$62638),1)  or

=Min(if(Achieve!\$B\$2:\$B\$62638=Sheet2!C5,Achieve!\$K\$2:\$K\$62638))
for the minimum value and

=Large(if(Achieve!\$B\$2:\$B\$62638=Sheet2!C5,Achieve!\$K\$2:\$K\$62638),1) or
=Max(if(Achieve!\$B\$2:\$B\$62638=Sheet2!C5,Achieve!\$K\$2:\$K\$62638))

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).

--
Regards,
Tom Ogilvy

Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Hi Tom That's perfect, Thanks for your help Ben

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

#### Tom Ogilvy

##### Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

##### Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.