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)

DateCDMANameCell


<tbody>
</tbody>
3/15/201868764Buena Vista845ASMB 1, RRH 6, TX VSWR ALARM MINOR
3/14/201868780Holly852ASMB 1, RRH 5, DIVERSITY IMBALANCE RX1/RX2 MINOR ALARM
3/12/201868878Pembroke854ASMB 1, RRH 5, DIVERSITY IMBALANCE RX1/RX2 MINOR ALARM
#N/A68882Vinton / Seibel Rd.860ASMB 1, RUI 1, PORT 2, FRAMING ERROR MAJOR
#N/A68882Vinton / Seibel Rd.860ASMB 1, RRH 2, INDETERMINATE CRITICAL
#N/A68882Vinton / Seibel Rd.860ASMB 1, RRH 2, CARRIER 1, PAF 2, NO PILOT

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

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

cerfani

Well-known Member
Joined
Dec 15, 2014
Messages
1,136
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:

HydeK

New Member
Joined
Mar 15, 2018
Messages
3
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

Well-known Member
Joined
Dec 15, 2014
Messages
1,136
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:

HydeK

New Member
Joined
Mar 15, 2018
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,499
Messages
5,529,217
Members
409,857
Latest member
KailuaTown
Top