Issues with Index Match formula returning values and some #ref

**kokapelly** · 01-27-2014, 03:38 PM

Hi All,

Am truly confused..(Nothing new)
I have two tabs (In-Transit) & (AC) column A in both sheet have a "Concatenate" that i copied and pasted and values only and also switch the formate to "Text" on both tabs. I do a vlookup

Please Login or Register  to view this content.

and it returns the results i want....But then i was thinking due to the volume of data what if the value in column A is found twice on tab "AC".. i wouldn't know with a "Vlookup".

So trying to build a Index/Match formula

Please Login or Register  to view this content.

i also returns some good results, but i also get some back as #REF. I also get #NA and both of the formulas and those are valid no matches...

**Pepe Le Mokko** · 01-27-2014, 03:40 PM

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. 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 demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**Tony Valko** · 01-27-2014, 04:47 PM

If you want to know if the lookup value appears more than once just do a simple COUNTIF:

=COUNTIF(A2,AC!A$2:A$15247)

If the result is greater than 1 what should happen?