Lookup with multiple criteria and matches

Mod. edit: below originally posted on thread: http://www.excelforum.com/excel-prog...la-needed.html

Hi,

I need help! I am trying to lookup for values which match multiple criterias but i can't seemed to get the formula right. Sample attached.

Thanks

cplian, welcome to the board.

Please note we do not permit members to ask questions in the thread of another member. If you have a question to ask please do so via your own thread. If you feel another thread is particularly relevant to your question please add a link to it in your own post.

Given this is your first post I have on this occasion moved your post to a new thread. Going forward please be sure to bear the above and all other Forum Rules in mind when posting.


Regards your question - could you first clarify the following:

Originally Posted by cplian

Re: Match Pair 14

Follow New - since there's no positive amount for New - to follow the highest negative amount - inception date

In your example you state that "highest" negative is -50k rather than -2k; that is to say you should use the greatest absolute value as opposed to the greatest actual value (-2 being in fact greater than -50)

My question would then be: if in addition to the above values you had 1k (positive) would the 1k take precedence over the -50k ?

In other words - greatest absolute value takes precedent if all values are negative else greatest positive value takes precedent, is that correct ?

Assuming the above to be correct I'd be inclined to conduct a helper calculation to simplify the process...

If we assume the data is sorted by Match Pair (and nothing else) then using your sample:

Please Login or Register  to view this content.

At which point:

Please Login or Register  to view this content.

The above looks worse than it is - in reality it's pretty efficient and given the data is sorted you need only conduct the calculation once per "Match Pair" [first instance] given all subsequent instances will use the same result.

The above formulae replicate the "expected" results in the sample.

Obviously use of Column J is for sake of demo. only - you can move this calculation as you see fit, however, you must remember to adjust references in the Col F calculation accordingly.