INDEX/MATCH works inconsistently, gives some correct values then #N/A

[scottryan]

I have a sheet called ROPdata that looks up matching values from another sheet in order to create a graph.

ROPdata

	B	C	D
#N/A	#N/A
#N/A	#N/A
#N/A	#N/A
#N/A	#N/A
#N/A	#N/A
#N/A	#N/A
#N/A	#N/A
#N/A	#N/A
#N/A	#N/A
#N/A	#N/A
#N/A	#N/A
#N/A	#N/A
#N/A	#N/A
#N/A	#N/A
#N/A	#N/A
#N/A	#N/A
#N/A	#N/A

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]Depth[/TD]
[TD="align: center"]ROP (min/m)[/TD]
[TD="align: center"]Gas (%)[/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: center"]400.0[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"]2.0[/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: center"]400.2[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"]2.1[/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: center"]400.4[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"]2.2[/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: center"]400.6[/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: center"]400.8[/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: center"]401.0[/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: center"]401.2[/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: center"]401.4[/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: center"]401.6[/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: center"]401.8[/TD]

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

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

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

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

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

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

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

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

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

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

</tbody>

The formula that I'm using to return the ROP values is =INDEX('ROPGasImport'!B:B,MATCH(B2,'ROPGasImport'!A:A,0)) and a similar one for the gas values. It works and returns the proper values until it gets to the 400.6 Depth value and then it gives the error message. The Depth column is created using =PreviousCell+0.2 to produce regular intervals. If I manually type in 400.6 in B5, the correct values are returned until it comes to 401.6 and then I get the #N/A again. I also tried starting the depth interval at 400.2 to see if still gave 3 correct returns before the error but it gave 2 and then an error from 400.6 on again, so it doesn't seem to like when the Depth is ***.6 unless it's manually typed in.

I tried SHIFT-ALT-ENTER when typing in the formula and also using --B2 in the match equation in case it wasn't being recognized as a number but those didn't fix anything. I could use VLOOKUP but I'd like to see if anyone has an idea why this isn't working first.

Excel tables to the web >> Excel Jeanie HTML 4

 

[Aladin Akyurek]

Perhaps...

=INDEX('ROPGasImport'!B:B,MATCH(ROUND(B2,1),'ROPGasImport'!A:A,0))​

 

[scottryan]

That fixed it! Thanks so much for the help - I'll remember that for next time.

 

[Aladin Akyurek]

That fixed it! Thanks so much for the help - I'll remember that for next time.

You are welcome. Thanks for providing feedback.