Index Match Formula - error when data source is moved to a different column

I had a slight problem with my formula yesterday and this was corrected and working as per:

Please Login or Register  to view this content.

I then moved my search data from column A to column B and modified my forumla to:

Please Login or Register  to view this content.

Now it doesn't work anymore. I cannot see what I have done to make this happen?

Should B2 remain the same?

Thanks for having a look. Yes, B2 is good. It's on the local page and the data has not moved.

Hi ids, it will help if you could elaborate more on how it doesn't work anymore?
Have you try evaluate the formula to see which part not working as expected, perhaps due to formatting etc...



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Good point Alvin chung! I am seeing #NA where I was seeing a value before. All cells involved are formatted as 'General'.

I assume you're seeing #N/A when evaluating the MATCH formula?
In B2, try press F2 and ENTER?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Originally Posted by alvin-chung

I assume you're seeing #N/A when evaluating the MATCH formula?

Yes thanks. #NA is the result in the cell where the formula is located.
In B2, pressed F2 then enter and everything stayed the same as before. Tried saving file and still the same.

Could you verify manually whether you can match value in B2 with values in 'C:\2449\Temp\[401201.xls]401201'!$B$2:$B$497?
If there's no match, the #N/A is the correct end result. Unless you want to get rid of the #N/A if no match is found, try IFERROR function.



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Originally Posted by alvin-chung

Could you verify manually whether you can match value in B2 with values in 'C:\2449\Temp\[401201.xls]401201'!$B$2:$B$497?

OK cool - yes, the match is present. Result is only returned correctly if it is in column A. #NA is returned if I move it to column B and change the formula accordingly.

What's the point of matching B2 to a list of B2:B1000? I mean the value B2 will always exist in B2 where you start to "scan" so you will always get this row.

B2 is the search string located in the local sheet. The search takes place in another spreadsheet as per the path stated in the code. It just so happens that in this instance, they are both column B.

Ok, then there is no way one works and the other doesn't! Check again the syntax for referring to another file, the extension (xls, xlsx etc).

Changed the path from C:\ to Z:\ (mapped network drive path) and now both formulas work ok. One has path C:\ for file location (match column A) and the other has path Z:\ for file location (match column B). I'll change over all the formulas now and see if it holds up.

Glad you figure it out, good luck



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Thanks for the assistance from everyone. Still doesn't make sense but will update if I find an explanation.