Index match help

[Virtaava]

Help finding values from columns that match criteria.
If cell in J column has value of S find next value B from same column downwards, then find from that row Close add found value to L column below the row that gave signal S OR if the cell in J column has value of B find next cell downwards that has value of B and from that row find Close and add found value to column M below the row that gave the signal.

[TABLE="width: 1074"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 1000, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[TD]l[/TD]
[TD]m[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Open[/TD]
[TD]Low[/TD]
[TD]High[/TD]
[TD]Close[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Signal[/TD]
[TD]Price of signal[/TD]
[TD]S cloce[/TD]
[TD]B Close[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]0,9305[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]0,9348[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]0,9264[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0,9348[/TD]
[TD]Value of cell F6 from the calculations[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]0,9338[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66"]0,9292[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]0,932[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0,9292[/TD]
[TD][/TD]
[TD]Value of F10 From calculations[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]0,9322[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66"]0,9293[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]0,9146[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]0,9114[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]0,9146[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thank you for your help. I just cant wrap my head around the index match function

 

[Peter_SSs]

Welcome to the MrExcel board!

See if these, copied down, do what you want.
Note the error in cell L11. What do you want returned in a case like this where there are no more "B" values below?

Excel Workbook
	F	G	H	I	J	K	L	M
1	Close				Signal	Price of signal	S cloce	B Close
2	0,9305
3	0,9348				S
4	0,9264					0,9348	0,9292
5	0,9338
6	0,9292				B
7	0,932					0,9292		0,9146
8	0,9322
9	0,9293
10	0,9146				S
11	0,9114					0,9146	#N/A
12
INDEX MATCH

 

[Virtaava]

Welcome to the MrExcel board!

See if these, copied down, do what you want.
Note the error in cell L11. What do you want returned in a case like this where there are no more "B" values below?

INDEX MATCH

	F	G	H	I	J	K	L	M
Close				Signal	Price of signal	S cloce	B Close
			S
			B
			S
				#N/A

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:17px;"><col style="width:17px;"><col style="width:17px;"><col style="width:56px;"><col style="width:90px;"><col style="width:69px;"><col style="width:60px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]0,9305[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]0,9348[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]0,9264[/TD]

[TD="align: right"]0,9348[/TD]
[TD="align: right"]0,9292[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]0,9338[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]0,9292[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]0,932[/TD]

[TD="align: right"]0,9292[/TD]

[TD="align: right"]0,9146[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]0,9322[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]0,9293[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]0,9146[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]0,9114[/TD]

[TD="align: right"]0,9146[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula L2 =IF(J1="S",INDEX(F3:F$20,MATCH("B",J3:J$20,0)),"") M2 =IF(J1="B",INDEX(F3:F$20,MATCH("S",J3:J$20,0)),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Sorry about this I probably didn't make my self clear enough in the first post.

So the column J the S and B signals can come in any cell in that column and the rows that are currently in my sheets is about 6000. I get everyday new row with data to columns a to f. and if the calculations in G H and I are true the can generate new B or S signal to Column J
And for the error message blank if there is no signal.

 

[Peter_SSs]

So the column J the S and B signals can come in any cell in that column and the rows that are currently in my sheets is about 6000. I get everyday new row with data to columns a to f. and if the calculations in G H and I are true the can generate new B or S signal to Column J

I'm not sure what you are saying. Does my formula produce incorrect results? If so, which cells are incorrect and exactly what results should show in what cells?

 

[Virtaava]

I'm not sure what you are saying. Does my formula produce incorrect results? If so, which cells are incorrect and exactly what results should show in what cells?

Sorry your formula is working fine. I just got #name ? error. My excel is in Finnish so it did't understand the formula i had to replace IF with JOS, INDEX with INDEKSI, MATCH with VASTINE and , with ;
Now it's working fine. thank you for your help.

 

[Virtaava]

Ok the formala is working witout errors but it is giving wrong information.
maybe picture will help because i can't post attachments. https://imgur.com/a/Wk3CBRf

So if B signal find Close value of next S signal.
And if S signal find next B signal close value.

Thanks

 

[Peter_SSs]

I can't see all of your formula but when I first replied, you didn't say that you had 6000 rows and showed 11 so I used $20 in my formulas. That 20 needs to be a row number greater then the last row of your data. So perhaps change to $7000 to be sure then copy down. Also make sure you have the $ signs in the right place.

"i can't post attachments" Nobody can, but see the link in my signature block below for suggestions for posting small screen shots, including formulas (like mine in post #2 above).