VLOOKUP Formula works on one sheet but fails on second sheet

[MrBrianS]

Hello, I have a multi-page workbook. It is an Excel 2010 .xlsx version.

I have a copy of a sheet I'm using for play/work and have a vlookup referring to a a named ranged in a second file that works fine on the original page, but on the copied sheet, the exact same vlookup formula cannot find the value I'm looking for in the external file. There are no macros/VBA code involved, the formula Transitions is unchecked, everything about the two sheets is the same, including the static values and formulas.

Example: VLOOKUP($A2,OTHERFILE.xlsx!namedrange,19,FALSE)

Finds reference and returns value on the first sheet, but not the other.

Thank you

 

[Special-K99]

Manually locate the value.
Copy the other value into a blank cell.
Do something like

=(A1=B1)

If it returns TRUE then the problem lies with your formula.

 

[MrBrianS]

Manually locate the value.
Copy the other value into a blank cell.
Do something like

=(A1=B1)

If it returns TRUE then the problem lies with your formula.

The formula is exactly the same on both sheets...no differences at all, and it's a formula that I use all of the time with no problem. The only "symptom" is that there seems to be a certain row where the formula stops working. It fails down the rest of the sheet in that field.

There are no $set rows in the formula, just looks in a named range in the other file and all the rows above this cell work fine. I've double-checked that the cell that vlookup is looking for in the first parameter is the correct cell also.

Thank you anyway...

 

[Special-K99]

Can yopu post the files?

Attach a sample spreadsheet, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[dUBBINS]

Are you sure your named range doesn't end where your error begins?

 

[MrBrianS]

No, the named range includes all items in the external file. I'm about to try and post examples...thanks

 

[MrBrianS]

Can yopu post the files?

Attach a sample spreadsheet, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

I'm hoping that these pictures will suffice since it says not to upload actual spreadsheets.

 

[MrBrianS]

Can yopu post the files?

Attach a sample spreadsheet, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

Here are the simplified Excel files if anyone wants to view them:

http://www.briansnotes.com/images/named_range_file.xlsx
http://www.briansnotes.com/images/vlookup_file.xlsx

 

[dreid1011]

I don't know if this is any issue, but the Non-Working sheet row 7 MPC does not have a leading 0 and the Working sheet row 7 MPC does have the leading 0. The same item in the third image, named range dal, also has a leading 0. Maybe number/text issue?

 

[MrBrianS]

That may explain it normally, but does not explain the cells below, and they are Text format. I will check again though...thanks for noticing that.