Index and Match Combination, Lookup Functions

The attached file shows a collected data series in Excel (depth (A)/value (B)) alongside a full depth series (E) with 2 functions; Lookup (F) and Index+Match (G).

1) The green cells are returning correct results. The red cells are returning incorrect results. Can you spot why these red cells are not returning the expected values?

2) I'm using a rather complex way of removing the duplicates (P). I filter by "FALSE" and replace everything with "0". Not as essential, but can I stop the repeats by adding something into the previous function to save this step?

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

For some reason, not sure why, some of those numbers aren't seen as numbers. Easy way is to select all the data in column A, goto the Data ribbon, and pick Text to Columns. Then just OK your way through to the Finish. Should fix all the errors.

You're welcome.