I am becoming sad that no one has answered this question, which I myself found quite interesting when I was in grad school studying Viking-adjacent topics, so I will provide an answer based on what I learned then and checked against more recent sources that I hope will satisfy the OP's curiosity and this subreddit's standards.

Basically, the L'Anse aux Meadows site was found because people who knew what to look for and had an unconventional idea of where to look for it were able to access local knowledge about a site scholars were not even aware existed.

Before the site was located, the Norse (I will use that term in preference to "Norman" since the latter tends to be restricted to the Norse who settled in northern France and their descendants) visits to and short-term settlement of North America west of Greenland were known only through literary sources. Specifically, there were two accounts in the corpus we refer to as the Icelandic sagas. These were written versions of traditions that had existed in oral form for centuries. Although differing in many details, both said that the Greenland settlers had located and made brief attempts to settle a region to the west called Vinland. In addition, the chronicler Adam of Bremen mentioned in a historical work composed around 1073 that the king of Denmark had told him that an island called Vinland had been discovered in the West. (Of course the king had not been there himself; he heard about it from others, possibly at multiple removes from the actual eyewitnesses.)

Where was Vinland, if it was even a real place? One etymology of the word is "vine land," as in grapevines. Adam specifically stated that Vinland was so-called because grapes grew there, and the sagas also included grapes among the local products the settler returned to Greenland. Thus, the majority of scholars thought that Vinland meant Grapevineland and indicated a land where there were grapes growing. So most people who believed these stories referred to real events in a useful way (as opposed to hopelessly distorted in transmission or referring to a kind of fairy-land rather than a real place) thought Vinland had to be pretty far south on the North American coast, maybe Massachusetts, because that is where they thought you could find wild graps.

There was a minority view among scholars that the Vinland had a different etymology under which it meant Meadowland (different vin- element) rather than Grapevineland. So maybe it was actually Meadowland and was later misunderstood as Grapevineland.

What about the grapes in the accounts? Well, maybe the medieval sources themselves were confused about the meaning of Vinland and, after adopting the grape interpretation, added details about grapes. Maybe the grapes had really existed but had come from elsewhere: the sagas have the Norse moving around a lot in the lands they visited.

In addition, some people argued that grape-like fruits grow as far north as Newfoundland, so even if Vinland is Grapevineland it could still be further north than mainstream opinion held. So both minority views (again, among those who thought these literary sources were talking about real-world events in a useful way) supported looking a bit further north.

In any case, until 1960 nobody had found any remains that could be conclusively linked to the Norse. That was not for lack of trying, either in the form of deliberate hoaxes or misinterpretations of Native American or settler sites, mostly in New England.

Among those trying to find the Vinland settlement farther north were a Norwegian couple, Helge Ingstad (1899-2001) and Anne Stine Ingstad (1918-1997). Helge, an explorer and established popular author who had lived in the Canadian Arctic, Greenland, and Svalbard (the latter two as governor), had long been interested in the Vinland settlement. He believed in the Meadowland interpretation and had previously visited many potentially relevant locations. Anne Stine (it is a compound given name) was a university-trained archeologist. Anne Stine's scholarly interests veered much earlier than the Viking Age.

For those who, like the Ingstads, believed in a northern Vinland, northern Newfoundland seemed like a good place to look. This was not an entirely new idea. W.A. Munn, a newspaper publisher in St. John’s, had suggested in a series of articles printed in 1914 that the Vinland settlement was probably in northern Newfoundland, though his writings were not widely circulated. Finnish scholar Väinö Tanner had published a similar theory in 1939 and it was widely read at least within the community interested in such things.

Danish archeologist Jørgen Meldgaard, a specialist in Arctic sites who believed the answers to the Vinland question were in Labrador and northern Newfoundland, was hot on the trail. He had done some exploratory work in the area in 1956 that included test excavations and extensive travels by boat and air. Meldgaard had in fact been in contact with Helge for several years and both knew Tanner's theory. Both Meldgaard and Helge were interested in artifacts found in Greenland that seemed to be of Native American (not Inuit and not Norse) origin, since they were evidence of Norse-Native American contact and, thus, the reality of the Vinland stories. But Meldgaard, despite some promising leads that convinced him he was close, came up empty in his 1956 expedition. He planned to return when he could secure sufficient funding.

But here’s what actually happened. In 1960, Helge (accompanied by the couple’s teenage daughter Benedicte, who’s now an anthropologist) visited Newfoundland's Northern Peninsula by boat to investigate sites that might be of interest, as part of Helge's general pattern of doing such things when he had time. Anne Stine, a working archeologist, did not come initially because she had her job in Norway. The Ingstads believed that the settlement would be on a small island or peninsula close to a source of fresh water, because that is the kind of place the Norse tended to choose. So Helge looked for places like that.

Part of Helge’s method during his explorations was to ask the locals about unusual structures or ruins in suitable places. In L’Anse aux Meadows, Helge met George Decker, patriarch of one of the few families left in the tiny fishing village, who told him about some grass-covered mounds on his property that were reputed to be an “old Indian camp.” This was exactly the kind of thing Helge was were looking for: a possible habitation site that nobody could link to known settlement history, in proximity to the shore and water sources. Upon seeing the site Decker had spoken of, Helge was convinced it was not a Native American site but instead the Norse one he was seeking.

Anne Stine came to L'Anse Aux Meadows in 1961 to conduct excavations. In the first year, these revealed sufficient similarity to Norse sites elsewhere in the North Atlantic region and artifacts that the Norse would have brought. In October 1961, Helge announced the discovery of Vinland and the specific settlement found in the texts. The authenticity of the site was pretty quickly accepted. Anne Stine continued to lead excavations until 1968.

So that’s how L’Anse Aux Meadows was found. Basically, people who were very interested in this question and had a good instinct about where to look poked around the right places and found someone who knew where the site was but not what it was. In fact, Munn's guesses had been very close but there is no evidence he or anyone else associated Decker's "old Indian camp" with the Norse (despite many incorrect identifications of other sites as Norse). It also seems likely that Meldgaard would have located the site during his next visit to Canada. The Danish press actually misinterpreted some statements about Meldgaard's work as indicating he was the actual discoverer and had either found the site himself (but refrained from publicizing it early) or had guided the Ingstads to it (but not received credit). There was a war of words in the press over credit for the discovery that cooled relations between Helge and Meldgaard and weighed heavily on Anne Stine, who has probably never received the credit she was due.

We know a lot about this process from the Ingstads themselves. Helge wrote a number of pieces for popular audiences. Anne Stine wrote scholarly descriptions based on the excavations. Benedicte recently wrote a book about her parents.