VLOOKUP stops working mid sheet

HydeK

New Member
Joined
Mar 15, 2018
Messages
3
I have to provide a quick report every morning with a list of outstanding alarms on equipment. Based on this list troubles are prioritized and techs are dispatched to make repairs. This can mean that some of these alarms will remain on the list for varying periods of time until the tech is able to correct the issue. Using VLOOKUP I look back at the previous sheet in the workbook to pull the dates forward for any issues that have not been resolved. This worked great for weeks, but recently the VLOOKUP started to fail. It works for about 113 rows of alarms and then it quits bringing the dates forward. Using IFERROR it would insert the current day's date. The raw data comes in the form of text that delimited by a semi colon and is pasted into columns E & F, which is a unique number for the equipment and the alarm for that equipment. Column A is hidden and blank for future use, this puts the dates in column B. Can anyone help me understand why it just stops on row 113. The IFERROR has been removed because it was putting the current date in for everything.

=VLOOKUP(E111&F111,'03-15'!$A$3:$L$1000,2,FALSE)

[TABLE="width: 843"]
<tbody>[TR]
[TD="class: xl67, width: 89"]Date[/TD]
[TD="class: xl67, width: 64"]CDMA[/TD]
[TD="class: xl69, width: 256"]Name[/TD]
[TD="class: xl68, width: 33"]Cell[/TD]
[TD="class: xl67, width: 401"]

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 843"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]3/15/2018[/TD]
[TD]68764[/TD]
[TD]Buena Vista[/TD]
[TD]845[/TD]
[TD]ASMB 1, RRH 6, TX VSWR ALARM MINOR[/TD]
[/TR]
[TR]
[TD]3/14/2018[/TD]
[TD]68780[/TD]
[TD]Holly[/TD]
[TD]852[/TD]
[TD]ASMB 1, RRH 5, DIVERSITY IMBALANCE RX1/RX2 MINOR ALARM[/TD]
[/TR]
[TR]
[TD]3/12/2018[/TD]
[TD]68878[/TD]
[TD]Pembroke[/TD]
[TD]854[/TD]
[TD]ASMB 1, RRH 5, DIVERSITY IMBALANCE RX1/RX2 MINOR ALARM[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]68882[/TD]
[TD]Vinton / Seibel Rd.[/TD]
[TD]860[/TD]
[TD]ASMB 1, RUI 1, PORT 2, FRAMING ERROR MAJOR[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]68882[/TD]
[TD]Vinton / Seibel Rd.[/TD]
[TD]860[/TD]
[TD]ASMB 1, RRH 2, INDETERMINATE CRITICAL[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]68882[/TD]
[TD]Vinton / Seibel Rd.[/TD]
[TD]860[/TD]
[TD]ASMB 1, RRH 2, CARRIER 1, PAF 2, NO PILOT[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
E111&F111 creates a string value and if it returns N/A, then that string is not present in column A. If the value is in the data, but if a character is different in any way or the value is not a string type, then it wont be found.

What does
E111&F111 evaluate to? and how is this value listed in the data column A?
 
Last edited:
Upvote 0
E111 is the unique three digit number that designates the equipment in alarm. F111 is the alarm data that tells what is wrong with the equipment. They are what make up the raw data that is put into the spread sheet. The example provided was from two consecutive sheets set up just like two consecutive days would be in the working report. The only difference is that the same data was inserted in both sheets so that there should be no differences between the two sheets, but VLOOKUP still stopped bringing the dates forward on line 113.
 
Upvote 0
there is a difference between the values you concatenated from column E and F into a string and what you assume is the same value in column A

paste them here then

Paste the value in column E and F here and paste the value that should match here... you arent showing your data so no way to know if you are overlooking something or describing something inaccurately... N/A means your concatenated string is not in column A. Excel doesnt make mistakes. You have a formatting issue. You might need to trim the values but no way to know.
 
Last edited:
Upvote 0
Based on what you said above I have re-entered the concatenation formula and re-applied it to column A again and now it does appear to be working. It appears that some how the concatenation was corrupted. Thank you for your help.
 
Upvote 0

Similar threads

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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