Excel - Return value, after cell has been matched, between headers

Question

Here I am once again, asking for help on this great community. I hope I can retribute as much as you did someday.

The 4th column (D) in the picture is the expected output.

This is how I put the problem:

• If a number is negative / or has an A (number in the A row is always negative);

  • Use MATCH to check if there is a correspondent (positive) number in the range (between headers);

  • If there is a MATCH output the value of the ID (1st column) of the F that has been matched, and then, output the ID of the A that has been matched

• If not, do nothing or place a 0.

The problems are:

• It needs to loop, because the negative value can come after the positive one;

• The search must be contained between headers.

I've tried to modify and create my own solutions from the tips and codes of the other questions I've placed, but with no success..

Thank you so much!

Answer 1

Try pasting this into the immediate window of the VBE and hitting enter:

Names.Add "i", "=$A$1:$A$12": _
Names.Add "v", "=$B$1:$B$12": _
Names.Add "m", "=$D$1:$D$12": _
Names.Add "h", [IF({1},LOOKUP(ROW(i),ROW(i)/(v=INDEX(v,1))))]: _
Names.Add "l", [IF({1},LOOKUP(ROW(i),ROW(i)/(v=INDEX(v,1)),FREQUENCY(-ROW(i),IF(v=INDEX(v,1),-ROW(i),0))-1))]: _
[m]=[IF(COUNTIF(OFFSET(v,h,,l),"=-"&v),CONCATENATE("=",ADDRESS(ROW(i),COLUMN(i),4),"&"" AND ""&INDEX(",ADDRESS(h+1,COLUMN(i)),":",ADDRESS(h+l,COLUMN(i)),",MATCH(-",ADDRESS(ROW(v),COLUMN(v),4),",",ADDRESS(h+1,COLUMN(v)),":",ADDRESS(h+l,COLUMN(v)),",0))"),"")]: _
Names("h").Delete: _
Names("l").Delete

The output for the example data in the question is:

D5  =A5&" AND "&INDEX($A$2:$A$6,MATCH(-B5,$B$2:$B$6,0))
D11 =A11&" AND "&INDEX($A$11:$A$12,MATCH(-B11,$B$11:$B$12,0))

Note: This method uses named arrays to store intermediate results for header positions h and section lengths l and takes around 3-4 secs when the range names i (id), v (value) and m (match) are extended to 40,000 rows. A simpler formula for this is possible but it would be very inefficient to fill down on large data sets.

Answer 2

This is the formula I would use. Note that you'll have to explicitly reference the ranges for the headers. You may want to define named ranges to avoid typos and to ease readability. This formula would be put into D2 (fill-drag to D6).

=IFERROR(IF(B2<0,INDEX($A$2:$A$6,MATCH(B2*-1,$B$2:$B$6,0),1) & " AND " & A2,""),"")

How it works: The IFERROR is just to catch the #NA when there's no match. If the value B2 is negative (less than 0), then return the value in the first column of a row with a matching positive value. We use absolute references for the INDEX and MATCH since we don't want to look in A3:A7, A4:A8, etc. when the formula gets dragged down into the other cells. Only the value being compared and its corresponding ID should be relative (A2/B2), since we want that to change for each row.

Here's how it looks. Keep in mind that since my values are from A1:B6 my formula would be changed to INDEX($A$1:$A$6...MATCH(B1*-1,$B$1:$B:$6,0)...