Index & Match with multiple criteria



In the attached spreadsheet there are two named ranges (A1:A8 named 'SP' and B1:B9 named 'OI')

I need a number in 'SP' whose match in 'OI' is the second highest number excluding the Sum Total of 'OI' provided it is lesser than the number in 'SP' whose match in 'OI' is highest. Otherwise, formula should look for third highest or fourth highest number in 'OI' whouse match in 'SP' is lesser than the match of the highest number in 'OI'

It may be little difficult to understand from the above explanation. However, it can be understood very well from the attached spreadsheet.






Please try this formula. It's an array formula and therefore needs to be confirmed with CTRL+SHIFT+ENTER.


The formula uses the named range OII which is equal to your own range OI but excluding the total. This is because the two ranges used by the formula must be of identical size.

You can still keep your OI range even though it overlaps with OII. In fact, Excel allows you to name several ranges of identical address with different names - theoretically. However, I don't recommend any of the names SP, OI or OII because these are all valid column IDs. The point of naming ranges is to be able to instill meaning into the formulas where they are used. "SP" and "OI" probably make perfect sense to you but they are not equally descriptive to a third person, potentially confusing to Excel, and you might not be your same self when you look at the formulas a few years from now :-)

Edit Jan 14, 2018  ===========================

Coming to think of it, the above formula may not work outside the lab in which it was created because the value it correctly identifies may not be unique. The Lookup using that value may return a match from another row sporting the same value if it is higher up in the list than the one meant.

A VBA solution wouldn't have that problem. However, if you are restricted to worksheet functions you will need at least one helper column in which you create unique values. Then you can apply the above formula to work on those values, guaranteed to return the correct result because all values are unique.

To create unique numbers add a fraction of the row number to the cell value. Say, A1 and A2 both have the value of 25. Let the helper column have the formula =A1+(Row()/1000, copied down. Now the Helper shows values of 25.001 and 25.002. Row()/1000 will work if the maximum number of rows in your sheet is less than 1000 (up the ante to Row()/10000 if necessary) and provided your numbers don't have decimals themselves. Use variations of the idea to create unique values which don't falsify your data. Then use the row number found in the Helper to return data from the identical column with real numbers.


Answer the Question

You must create an account to use the forum. Create an Account or Login