Match Index with if and function for dataset in defined range

[trudon01]

Hi everyone,

I'm trying to extract a specific range of values (coordinates X in this case) from a given dataset, using the index match function.
The lookup needs to display each coordinate within that range successively. The function works if the and function and one of the conditions is removed, but not with both.

Any ideas as to what I'm doing wrong?

X	Y	Z		Limits	Z
-9143.991 <tbody> </tbody>	-7289.529 <tbody> </tbody>	1395.194 <tbody> </tbody>		Upper <tbody> </tbody>	1411 <tbody> </tbody>		{=INDEX($A$2:$C$98,MATCH(0,IF(AND($F$2>=$C$2:$C$98,$F$3<=$C$2:$C$98),COUNTIF($H$1:$H1,$A$2:$A$98),""),0),1)} <tbody> </tbody>
-9142.708 <tbody> </tbody>	-7450.554 <tbody> </tbody>	1406.76 <tbody> </tbody>		Lower	1408.359 <tbody> </tbody>		{=INDEX($A$2:$C$98,MATCH(0,IF(AND($F$2>=$C$2:$C$98,$F$3<=$C$2:$C$98),COUNTIF($H$1:$H2,$A$2:$A$98),""),0),1)} <tbody> </tbody>
-9141.267 <tbody> </tbody>	-7466.744 <tbody> </tbody>	1408.359 <tbody> </tbody>					{=INDEX($A$2:$C$98,MATCH(0,IF(AND($F$2>=$C$2:$C$98,$F$3<=$C$2:$C$98),COUNTIF($H$1:$H3,$A$2:$A$98),""),0),1)} <tbody> </tbody>
-9138.91 <tbody> </tbody>	-7493.216 <tbody> </tbody>	1409.407 <tbody> </tbody>
-9136.395 <tbody> </tbody>	-7374.848 <tbody> </tbody>	1400 <tbody> </tbody>
-9134.054 <tbody> </tbody>	-7523.171 <tbody> </tbody>	1411.755 <tbody> </tbody>
-9127.053 <tbody> </tbody>	-7566.355 <tbody> </tbody>	1412.451 <tbody> </tbody>
-9122.349 <tbody> </tbody>	-7578.854 <tbody> </tbody>	1412.004 <tbody> </tbody>
-9113.401 <tbody> </tbody>	-7602.633 <tbody> </tbody>	1410.763 <tbody> </tbody>
-9104.005 <tbody> </tbody>	-7613.578 <tbody> </tbody>	1409.307 <tbody> </tbody>
-9088.181 <tbody> </tbody>	-7632.145 <tbody> </tbody>	1408.359 <tbody> </tbody>
-9065.29 <tbody> </tbody>	-7660.803 <tbody> </tbody>	1401.617 <tbody> </tbody>
-9056.56 <tbody> </tbody>	-7673.477 <tbody> </tbody>	1400 <tbody> </tbody>
-9034.104 <tbody> </tbody>	-7685.028 <tbody> </tbody>	1397.697 <tbody> </tbody>
-9031.84 <tbody> </tbody>	-7688.68 <tbody> </tbody>	1397.42 <tbody> </tbody>

<tbody>
</tbody>

etc

Thanks for trying to assist in advance.

Rgds

 

[Aladin Akyurek]

Care to describe where we are heading in words without any reference to a formula?

 

[trudon01]

Hi Aladin,

The formula mentioned is given in the spreadsheet above (albeit the spreadsheet changed formatting upon posting).
I currently have a large database of coordinates XYZ.
The aim was to define the upper and lower elevation (Column E) values, and subsequently extract all the X then Y (Column A & B) values in that range.

I found that this works, but duplicates all results.
=INDEX($A$2:$C$98,MATCH(1,(IF($F$2>=$C$2:$C$98,COUNTIF($H$1:$H1,$A$2:$A$98),""))*(IF($F$3<=$C$2:$C$98,COUNTIF($H$1:$H1,$A$2:$A$98),""))),0)

Any suggestions?

Thanks