Kindly help me with a formula to find the number in column 'C' which has max value in coumn 'B' with a condition that the number in column 'C' should be greater than 'A1'. In the attached file max value in column 'B' is in cell 'B8'. However, I'm not interested in it as the number in column 'C8' is 135 which is lesser than 'A1' i.e. 145. The result I'm looking for is '150' in cell 'C11' as the value in column 'B11' is max for a number greater than 'A1' in column 'C'
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Max with Index and Match
0
Answers
0
Selected Answer
The hardest part of your task is to formulate the question "correctly", in the sense that it must be understood by Excel. Your formulation, "Find the value in column C where column B has the largest value And column C > A1" turned out to be a red herring. When you formulate like "Find the largest value in column B where the value in column C is larger than A1" you start making headway because once you have that value you can find the value you want in column C with a simple VLOOKUP.
For my trials I created three named ranges as follows
- ClmB = B1:B21 (on your sample worksheet)
- ClmC = C1:C21
- Tbl = B1:C21
The VLOOKUP formula is, as promised, simple:-
=VLOOKUP(254675,Tbl,2,0)This is the formula which finds the lookup value.
=MAX(IF(ClmC>A1,ClmB))It's an array formula and must therefore be confirmed with CTRL+SHIFT+ENTER. Observe that the missing (optional) FALSE result for the IF statement just returns "False" by default which the MAX function interprets as zeroes.
Now all you have to do is to insert the array formula that finds the lookup value into the VLOOKUP formula you already have. Here is your final formula.
=VLOOKUP(MAX(IF(ClmC>A1,ClmB)),Tbl,2,0)This is an array formula, too, because the MAX/IF formula is. Confirm with CTRL+SHIFT+ENTER. When entered (confirmed) correctly, Excel will display the formula between curly braces in the formula bar. Otherwise you will get a #N/A error.
Generally speaking, named ranges not only serve to make formulas easier to read and understand, they also make formula management a lot easier because as ranges change they can be modified in the Names Manager instead of having to change thousands of formulas that refer to them. Building a dynamic range definition in one location is also more efficient than doing the same in every formula. However, with that being said, you can always replace the names in the formulas with the range addresses they represent. Remember to replace ClmB with $B$1:$B$21 (absolute addressing) if you intend to copy the formula anywhere.