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

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.
 
Upvote 0
...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 :(
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top