Help to Update INDEX MATCH Formula to Handle More Rows

Hello,

I created an excel document that automatically pulls data from a few different sources. For one of these sources, I simply paste the new data in, and the columns to the right of the data with all the formulas pulls the appropriate information. However, I originally created the formula to pull 159 rows in the match index. I tried to update this amount to 700, and I am getting a 0. In fact, when I change these values to be less than 159, I now get a 0. I'm not sure how this function works with the document and why it wont just pull the information I need. Here is the formula:

=IFERROR(INDEX($D$2:$D$159, MATCH(0, COUNTIF($AD$1:AD1,$D$2:$D$159), 0)), "")

The information in column D is what I want. Within Column D are several values that are the exact same, and I'm attempting to only pull the unique values from the list. Let me be clear, this formula currently works, but it doesn't work if I try to increase the amount of rows it checks. I know I can simply do a workaround by copying these values and using conditional formatting, use this formula: =COUNTIF($B$3:B3,B3)>1 Then just simply remove the duplicate values and copy and paste the list where I need it, but I would rather not have to do that every time, as I would also have to do it for another Index, Match, Countif Function on the spreadsheet. I'm open to not using and Index, Match formula as well. It seems problematic. Please Help!

Welcome to the forum.

Administrative Note #1:

Please be aware that this is a free forum and that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Your particular request may be important to you, but your request is no more important here than anyone else's. Thanks for your understanding and patience.

Administrative Note #2:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I will do it for you this time.)

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.

Please update your user profile to show the version of Excel that you are using. Windows 10 is the operating system and is of no interest to us. Thanks.