VLookup Formula Skips Rows and Starts Again

zgadson

Board Regular
Joined
Jul 16, 2015
Messages
68
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Problem: VLookup formula stops working midway through, then starts again after 40 lines. Every column has a formula top to bottom, every cell type is the same, and there are no blank spaces before or after any cell text.

Scope: My worksheet contains data for my warehouse layout. The first column is the Aisle and the 2nd column is the rack location within the aisle. In order to keep tabs on where product belongs in the warehouse, all product is assigned to various aisles and rack locations. I have a user input table that I want to use to populate an "Assignment" column - in other words, the user will enter an aisle number and start and stop numbers for rack locations, which will then populate this column.

IE, if a user assigns my customer, American Sugar Refining to to Aisle GG3, they then select the START and STOP (first and last) locations in the aisle. However (plot twist!), to make it easier for my users, once an aisle is selected (from a pre-set named drop down list) the START and STOP entry cells are dynamic drop-down lists based on which aisle is selected.

That said, let's say, per my example below, that a user assigns my customer American Suger Refining to aisle GG3, slots 1A through 57D (letters designate the rack level), I need that assigned customer name to appear in the column associated with it's rack location on my other sheet.

I have no earthly idea why what I have so far is only working for part of my workbook but it's incredibly annoying and I would absolutely love some help.

This is my entry form:
BUILDING 1 - STORAGE.xlsm
EFGHIJ
3ACCOUNTAISLEAISLE.TMPSTARTSTOPLEVELS
4AMERICAN SUGAR REFINING INCAisle_GG3GG3GG3_1AGG3_57DALL
5AMERICAN SUGAR REFINING INCAisle_GG4GG4GG4_1AGG4_40DEXCLUDE D
ASSIGNMENTS
Cell Formulas
RangeFormula
G4:G5G4=RIGHT([@AISLE],LEN([@AISLE])-SEARCH("_",[@AISLE]))
Cells with Data Validation
CellAllowCriteria
F4:F5List=Table_AisleList
H4:H5List=INDIRECT(F4)
I4:I5List=INDIRECT(F4)
J4:J5List=AISLES!$C$2:$C$4



This is effectively my background data:
BUILDING 1 - STORAGE.xlsm
ABCDEFG
5620GG3GG3_8CRACK8C1AMERICAN SUGAR REFINING INC
5621GG3GG3_8DRACK8D1AMERICAN SUGAR REFINING INC
5622GG3GG3_9ARACK9A1AMERICAN SUGAR REFINING INC
5623GG3GG3_9BRACK9B1AMERICAN SUGAR REFINING INC
5624GG3GG3_9CRACK9C1AMERICAN SUGAR REFINING INC
5625GG3GG3_9DRACK9D1AMERICAN SUGAR REFINING INC
5626GG3GG3_10ARACK10A1#N/A
5627GG3GG3_10BRACK10B1#N/A
5628GG3GG3_10CRACK10C1#N/A
5629GG3GG3_10DRACK10D1#N/A
5630GG3GG3_11ARACK11A1#N/A
5631GG3GG3_11BRACK11B1#N/A
5632GG3GG3_11CRACK11C1#N/A
5633GG3GG3_11DRACK11D1#N/A
5634GG3GG3_12ARACK12A1#N/A
5635GG3GG3_12BRACK12B1#N/A
5636GG3GG3_12CRACK12C1#N/A
5637GG3GG3_12DRACK12D1#N/A
5638GG3GG3_13ARACK13A1#N/A
5639GG3GG3_13BRACK13B1#N/A
5640GG3GG3_13CRACK13C1#N/A
5641GG3GG3_13DRACK13D1#N/A
5642GG3GG3_14ARACK14A1#N/A
5643GG3GG3_14BRACK14B1#N/A
5644GG3GG3_14CRACK14C1#N/A
5645GG3GG3_14DRACK14D1#N/A
5646GG3GG3_15ARACK15A1#N/A
5647GG3GG3_15BRACK15B1#N/A
5648GG3GG3_15CRACK15C1#N/A
5649GG3GG3_15DRACK15D1#N/A
5650GG3GG3_16ARACK16A1#N/A
5651GG3GG3_16BRACK16B1#N/A
5652GG3GG3_16CRACK16C1#N/A
5653GG3GG3_16DRACK16D1#N/A
5654GG3GG3_17ARACK17A1#N/A
5655GG3GG3_17BRACK17B1#N/A
5656GG3GG3_17CRACK17C1#N/A
5657GG3GG3_17DRACK17D1#N/A
5658GG3GG3_18ARACK18A1#N/A
5659GG3GG3_18BRACK18B1#N/A
5660GG3GG3_18CRACK18C1#N/A
5661GG3GG3_18DRACK18D1#N/A
5662GG3GG3_19ARACK19A1#N/A
5663GG3GG3_19BRACK19B1#N/A
5664GG3GG3_19CRACK19C1#N/A
5665GG3GG3_19DRACK19D1#N/A
5666GG3GG3_20ARACK20A1AMERICAN SUGAR REFINING INC
5667GG3GG3_20BRACK20B1AMERICAN SUGAR REFINING INC
5668GG3GG3_20CRACK20C1AMERICAN SUGAR REFINING INC
5669GG3GG3_20DRACK20D1AMERICAN SUGAR REFINING INC
5670GG3GG3_21ARACK21A1AMERICAN SUGAR REFINING INC
QUERY
Cell Formulas
RangeFormula
C5620:C5670C5620=IF([SLOT.DEPTH]=1, "RACK", "FLOOR")
G5620:G5670G5620=IF([@AISLE]=VLOOKUP([@AISLE],Table3[[AISLE.TMP]:[LEVELS]],1), LOOKUP(2,1/([@LOCATION] >= Table3[START])*([@LOCATION]<= Table3[STOP]),Table3[ACCOUNT]), "NO")
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,352
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Haven't looked carefully, so this is just a thought. Your formula:
=IF([@AISLE]=VLOOKUP([@AISLE],Table3[[AISLE.TMP]:[LEVELS]],1), LOOKUP(2,1/([@LOCATION] >= Table3[START])*([@LOCATION]<= Table3[STOP]),Table3[ACCOUNT]), "NO")
requires that Table3[[AISLE.TMP]be sorted in ascending order. If that's not the case, the Vlookup can return some unexpected results - i.e. is not reliable.
 

zgadson

Board Regular
Joined
Jul 16, 2015
Messages
68
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
...requires that Table3[[AISLE.TMP]be sorted in ascending order...

Thanks for the suggestion, however, I did try it with sorted data and it doesn't seem to make any difference :(
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,352
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thanks for the suggestion, however, I did try it with sorted data and it doesn't seem to make any difference :(
Bummer ...... thanks for the reply.
 

zgadson

Board Regular
Joined
Jul 16, 2015
Messages
68
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
If it helps to troubleshoot, I just tried my formula using HLookup instead of VLookup and it shifted the "#N/A" cells up two cells. Still wrong, but different wrong.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,123
Messages
5,570,315
Members
412,318
Latest member
angoeyuan
Top