VLOOKUP stops working mid sheet

[HydeK]

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)

Date

CDMA

Name

Cell

<tbody>
</tbody>

3/15/2018	68764	Buena Vista	845	ASMB 1, RRH 6, TX VSWR ALARM MINOR
3/14/2018	68780	Holly	852	ASMB 1, RRH 5, DIVERSITY IMBALANCE RX1/RX2 MINOR ALARM
3/12/2018	68878	Pembroke	854	ASMB 1, RRH 5, DIVERSITY IMBALANCE RX1/RX2 MINOR ALARM
#N/A	68882	Vinton / Seibel Rd.	860	ASMB 1, RUI 1, PORT 2, FRAMING ERROR MAJOR
#N/A	68882	Vinton / Seibel Rd.	860	ASMB 1, RRH 2, INDETERMINATE CRITICAL
#N/A	68882	Vinton / Seibel Rd.	860	ASMB 1, RRH 2, CARRIER 1, PAF 2, NO PILOT

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[cerfani]

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?

 

[HydeK]

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.

 

[cerfani]

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.

 

[HydeK]

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.

 

[cerfani]

No prob, glad you got it sorted