LOOKUP issue

[Dan Wilson]

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...

 

[Fluff]

What is your formula?

 

[Dan Wilson]

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]

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]

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]

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]

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]

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]

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]

Glad it's sorted & thanks for the feedback.