VLOOKUP acting weird

[nmccracken12]

I am sure this is an easy fix or easy answer. I created a VLOOKUP file a while back. It was intended to pull specific data from another workbook. This has worked almost flawlessly for years. Today, I am unable to pull any values for 5083 and up...

I have checked for spaces.
I checked the formatting and used format painter from working cells to paste to these cells.
I replaced 5083 with a smaller number (2032) that had been deleted off the sheet at some point. VLOOKUP functioned as expected. I changed the number back to 5083 and it's broken. The strange thing is it pulls a single column, but NOT what is actually in that column. Just a piece of it.

Any ideas?

 

[MountainFog]

Can you paste your formula and an example of what it is looking up?

 

[nmccracken12]

Can you paste your formula and an example of what it is looking up?

Sure. This is my formula --- =IFERROR(VLOOKUP(W28,'[MadVines Master Pricing Database Encompass Implementation Full List.xlsx]Master List'!$1:$1048576,3,FALSE),"") ---
It is just looking up the values on another workbook and it does that by the far left column of that workbook having a number in it. That is the number we use to find the VLOOKUP on the other sheet.

 

[MountainFog]

I replaced 5083 with a smaller number (2032) that had been deleted off the sheet at some point. VLOOKUP functioned as expected. I changed the number back to 5083 and it's broken. The strange thing is it pulls a single column, but NOT what is actually in that column. Just a piece of it.

Can you share a screenshot, or example data of both spreadsheets? I'm unclear as what is happening

 

[nmccracken12]

Can you share a screenshot, or example data of both spreadsheets? I'm unclear as what is happening

I am attaching screenshots of both sheets. The far left on database and the far right on VLOOKUP are what should match. I just noticed and you will notice it in the screenshot. 5080 pulled correct. 5081 and 5082 both are missing data that you can see should be there. 5083 and 5084 are missing almost everything except ml/btl. The ml/btl comes from a formula.

 

[MountainFog]

Can you screenshot your vlookup tab after pressing ctrl+~(this will show all your formulas). The trouble is that without you pasting with the xl2bb tool, I can't tell what is a formula and what it references.
Ideally it would be nice if you could capture the column headers in your screenshot so i know which column is which

 

[nmccracken12]

Can you screenshot your vlookup tab after pressing ctrl+~(this will show all your formulas). The trouble is that without you pasting with the xl2bb tool, I can't tell what is a formula and what it references.
Ideally it would be nice if you could capture the column headers in your screenshot so i know which column is which

This shows upload a mini sheet? Is there a way to just put the files on here? Attaching what you are asking for. Every column on the VLOOKUP is the same with a different index column number.

 

[Joe4]

You cannot upload files directly to this site. However, MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If you are unable to use that tool, many people will upload their files to a file sharing site (like DropBox, OneDrive, Google Drive, etc) and provide a link to it here in the thread.

 

[Alex Blakenburg]

Since you have MS365 you can try this. If it fixes your issue you have a data type mismatch:

=IFERROR(VLOOKUP(TRIM(W28),TRIM('[MadVines Master Pricing Database Encompass Implementation Full List.xlsx]Master List'!$1:$1048576),3,FALSE),"")

 

[MountainFog]

I believe you have spaces around the lookup value either in column W or column A in your database sheet. Try Alex's solution. If it works, there were spaces around your numbers