VLookUp with Duplicates

Trying to join one worksheet to another but the common key “UserID” has multiple entry rows in one of the worksheets. When I try to do your standard vlookup, I get “#NA” when it hits the first duplicate row and every entry thereafter…

Is there a way to simply have the duplicate row take on the value of the first successfully matched row?

Edit to add data provided by Renaudb3 in answer which I’ll delete.

Using 5.1 on Windows 10.

Please see attached, Mark.VLookUp Ex.ods

Can you add the formula you used? Also which version of LO and OS are you using? I could not get a duplicate key to give #NA. I’ve upvoted your question so perhaps you could attach a worksheet with demonstration of your problem.

It may be that you only need to add a fourth argument to VLOOKUP set to 0 for unsorted table.

Updated your example attached.
VlookUp Ex.ods

Changed formula in Cell C1 to :-

=VLOOKUP(B1,$Sheet2.$A$1:$B$300,2 )

This is then copied to the rest of the cells in column C.

Note that as B1 does not have $, it will change to B2 etc as it is copied to other cells in the same column. As the table to search does not change location for each cell that the formula is copied to you should add $, easiest way is to use the F4 key after selecting the source table in the formula.

As your source data is sorted you do not need to specify the fourth argument mentioned in my comment.

Thanks Mark. Your solution works!

But since I’m an amateur, I don’t really understand how that formula above works. Can you explain it in a bit more detail?

More specifically for $Sheet2.$A$1:$B$300 – why does Sheet2 need a dollar sign? And what does the period accomplish?

In the same way the dollar sign stops column or row in formula changing when you copy a cell with a formula to a different row or column, then the dollar sign in front of Sheet2 stops the sheet name from changing when you copy to a different sheet. In this example it wasn’t really needed as the formula is not being copied to another sheet, but using F4 to add dollar signs to A1 also added to the sheet name. In any case its good practice as the table is going to remain on Sheet2.

The perion is just the syntax for a reference to another sheet in the document, separates the sheet reference from the first cell of the range.