LOOKUP issue

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
361
Office Version
365
Platform
Windows
Good day. I am running Excel 2013 on Windows 10 Home. I have a workbook containing 7 worksheets. One of the worksheets uses LOOKUP to access a number from one of the other worksheets for each entry on the first worksheet. After entering new data in Row 95 of the worksheet, the LOOKUP function no longer works from that point forward. The formula is correct and the data on the other worksheet is there. Is there an issue with LOOKUP?
Apparently I am not allowed to add attachments to my posts. I have Print Screen images of the problem. Any help is appreciated.
Thank you,
Dan Wilson...
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,005
Office Version
365
Platform
Windows
What is your formula?
 

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
361
Office Version
365
Platform
Windows
Good day Fluff. Thank you for responding. The formula in question is =IF(B96="",0,LookupB96,Riders!G$1:EA$2))
Column B on the Rides worksheet contains ride numbers from 1 to 100. The ride number is prefaced with the year ex: 19-1 thru 19-100. Column H on the Rides worksheet contains the formulas used to look at the Riders worksheet. Row 1 on the Riders worksheet contains the ride numbers in cells G1 thru EA1. Row 2 on the Riders worksheet contains the number of riders for each ride above it. All of the formulas in Column H on the Rides worksheet are identical except for the reference to the B column. All of the formulas from H2 thru H95 work. The formulas in cells H96 thru H100 do not work. There are no rides entered yet beyond number 100.
Thank you,
Dan Wilson
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,005
Office Version
365
Platform
Windows
That formula works for me down to row 108 so there shouldn't be a problem. Double check that the ride numbers on both sheets are the same.
If you put this in an empty cell what does it say?
=B96=Riders!CW1
assuming that B96 should be picking up the value from col CW
 

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
361
Office Version
365
Platform
Windows
Good day again Fluff. I copied the workbook to a test file for experimentation. I re-entered the ride numbers on both the Rides and the Riders worksheet with no change. I entered

=Riders!CW2

into cell H96 on the Rides worksheet and it worked. I verified that the formats of both worksheets were correct. I opened up the workbook for 2018 that has 109 rides entered and everything worked correctly with the same formulas. I noticed that the Page Orientation on the Rides worksheet was set to Landscape. I changed it to Portrait, but no difference. In Landscape mode, Row 96 is the last row on the first page. The formula that you suggested does work, but there are several Macros in the workbook that sort the two worksheets by different Columns to get totals and averages. Using a direct access will not work. I don't understand why the Lookup function works on one workbook and not on another.
Thank you for sticking with this one,
Dan Wilson...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,005
Office Version
365
Platform
Windows
You need to put the formula
=B96=Riders!CW1
into a blank cell to check that the value in B96 is the same as the Value in Riders!CW1.
This assumes that the value in B96 is meant to be the same as the value in CW1, if not change CW1 to the cell that should match B96
 

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
361
Office Version
365
Platform
Windows
OK, I learned something today. I didn't know you could do that. When I first looked at the formula, I thought you had typed it wrong. I put the formula in an unused cell on the Rides worksheet and tried it on multiple ride numbers on the Riders worksheet. They all came back as True, above and below B96. To make sure the formula was working, I tried making the formula point to a different ride number than the reference and it came back False. I appreciate the extra effort on this.
Thank you,
Dan Wilson...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,005
Office Version
365
Platform
Windows
Ok, If that formula comes back as true, then the values match, so not sure why it's not working.
Does this work?
=IF(B96="",0,HLOOKUP(B96,G$1:EA$2,2,FALSE))
 

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
361
Office Version
365
Platform
Windows
EXCELLENT! That works! I modified the formulas in the test file and all 100 rides worked. I think I understand the difference between LOOKUP and HLOOKUP, but it really doesn't explain why LOOKUP is not working beyond ride number 95. It doesn't matter at this point as it works. Row I on the Rides worksheet also uses the same formulas to access the number of bikes on a ride. The HLOOKUP works there as well. LOOKUP did not work on Row I beyond ride number 95. The differences between the workbook for 2018 and 2019 was an increase in the total number of rides, riders, and bikes. Why LOOKUP works beyond ride number 95 in the 2018 workbook is still a question, but again, it doesn't matter. 2018 is working and it's closed. I truly appreciate your patience and extra effort on this one. I learned a couple things as I always do from this forum.
Thank you,
Dan Wilson...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,005
Office Version
365
Platform
Windows
Glad it's sorted & thanks for the feedback.
 

Forum statistics

Threads
1,081,575
Messages
5,359,715
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top