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?

 

[nmccracken12]

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),"")

I tried that formula and got the same results. However, using that caused this error to start popping up, "Excel ran out of resources while attempting to calculate one or more formulas. As a result these formulas cannot be evaluated." Could this be related to the original issue?

 

[nmccracken12]

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.

Here is a link to the two files. Let me know if it doesn't work. Excel

 

[Alex Blakenburg]

No the resource issue is because you should never run a vlookup that is effectively reading the entire spreadsheet ie $1:$1048576. Specify a Range of cells (rows & columns) be generous but not 1 million + rows and not all columns

 

[nmccracken12]

No the resource issue is because you should never run a vlookup that is effectively reading the entire spreadsheet ie $1:$1048576. Specify a Range of cells (rows & columns) be generous but not 1 million + rows and not all columns

Gotcha and I can fix that easily.

 

[Alex Blakenburg]

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

Not necessarily, I use Trim for number to text conversion because it handles a multitude of sins. By using it on both sides (lookup value and lookup array), it converts both sides to Text and as an added bonus cleans up spaces.

 

[Alex Blakenburg]

I think your duplicate data in your master pricing database is probably what is causing your issue.
The vlookup is going to return the first one it finds which is in many cases blank.

Book2
	A	B	C	D
1	QUOTE ID	Internal ID	Name	Count
2	8	112	4 Kilos Tierra De Mallorca	2
3	4144	2018	R. Stuart Vignette Pinot Noir	2
4	4144		Staglin Estate Chardonnay	2
5	8	3315	Terrassous Muscat Sec	2
6	5081		Alambique Serrano Barrel #263	2
7	5082		Alambique Serrano Navarre 7	2
8	5083			2
9	5084			2
10	5085			2
11	5086			2
12	5087			2
13	5088			2
14	5081		Alambique Serrano Barrel #263	2
15	5082		Alambique Serrano Navarre 7	2
16	5083		Cognac Hardy XO	2
17	5084	2508	El Dorado High Ester LBI/DHE Rum	2
18	5085	2509	El Dorado 18YO Bourbon & Cognac Finished Rum	2
19	5086	2510	El Dorado 18YO Bourbon & Oloroso Sherry Rum	2
20	5087	2516	El Dorado 12YR Rum 6pack AL	2
21	5088	2543	El Dorado 25YR Rum 1btl AL	2
Sheet1

 

[nmccracken12]

I think your duplicate data in your master pricing database is probably what is causing your issue.
The vlookup is going to return the first one it finds which is in many cases blank.

Book2
	A	B	C	D
1	QUOTE ID	Internal ID	Name	Count
2	8	112	4 Kilos Tierra De Mallorca	2
3	4144	2018	R. Stuart Vignette Pinot Noir	2
4	4144		Staglin Estate Chardonnay	2
5	8	3315	Terrassous Muscat Sec	2
6	5081		Alambique Serrano Barrel #263	2
7	5082		Alambique Serrano Navarre 7	2
8	5083			2
9	5084			2
10	5085			2
11	5086			2
12	5087			2
13	5088			2
14	5081		Alambique Serrano Barrel #263	2
15	5082		Alambique Serrano Navarre 7	2
16	5083		Cognac Hardy XO	2
17	5084	2508	El Dorado High Ester LBI/DHE Rum	2
18	5085	2509	El Dorado 18YO Bourbon & Cognac Finished Rum	2
19	5086	2510	El Dorado 18YO Bourbon & Oloroso Sherry Rum	2
20	5087	2516	El Dorado 12YR Rum 6pack AL	2
21	5088	2543	El Dorado 25YR Rum 1btl AL	2
Sheet1

I called myself looking for duplicates, but I did figure it out finally. I highlighted column A and selected sort and filter and CLEAR. Excel was not showing any sort of filter being used in that column, but it opened a bunch of blank cells with ml/btl filled in and it was the exact numbers having issues. Thank you for your help!

 

[Alex Blakenburg]

Thanks for letting us know. Glad we were able to help.
Reducing the data range used in your vlookup will be an added bonus and wouldn't have caused you performance issues at some point anyway.