CODEWITHSUNDEEP
excelexcel-formulavlookup
Pitbull999
Pitbull999

Reputation: 49

Vlookup with multiple conditions (negative / maximum value in the array)

I am trying to create a VLOOKUP with condition to fill up columns G and H.

The algorithm/condition is something like this.

Target: Lookup M value from Column C, by matching the Node.

  1. If Mz < 0, it goes to G (Take the Mininum value of Mz, i.e. largest negative Mz)
  2. If Mz > 0, it goes to H (Take the Maximum value of Mz, i.e. largest positive Mz)

enter image description here

I tried to create an array formula {=IF(MIN(VLOOKUP(F2,$B:$C,2,FALSE))<0,VLOOKUP(F2,$B:$C,2,FALSE),0)} But it doesn't seem to work properly.

Appreciate your enlightenment for this newbie. Thank you so much.

Upvotes: 0

Views: 978

Answers (2)

Rajesh Sinha
Rajesh Sinha

Reputation: 197

This solves the issue:

enter image description here

How it works:

  • An array (CSE) formula in cell D96:

    {=MIN(IF($A$96:$A$112=$C96,$B$96:$B$112))}

  • An array (CSE) formula in cell E96:

     {=MAX(IF($A$96:$A$112=$C96,$B$96:$B$112))}

N.B.

  • Finish formula with Ctrl+Shift+Enter & fill down.
  • Used groups are 1 to 5, you may include more.
  • Adjust cell references in the formula as needed.
  • Another solution should be Pivot Table to Find Max/Min Value In A Group.

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

For your version of Excel:

Negatives:  =AGGREGATE(15,6,1/($F2=$B:$B)*$C:$C,1)
Positives:  =AGGREGATE(14,6,1/($F2=$B:$B)*$C:$C,1)

enter image description here

Upvotes: 1

Related Questions