strange issue with pulling info to fill columns

[nzt101]

Hey guys,

i have an excel workbook, i've found a macro to use =vlookupneg, to look at columns to the left of the data cell im using.
the vlookupneg has worked perfectly on other sheets i'm setting up, but now i've got two worksheets that are filled with information loaded and transformed from 2 external xlsx files
im trying to put 2 columns of data from one sheet into the other sheet, details as below (ive compressed the columns that aren't neccessary)

=VLOOKUPneg(H2,Sheet2!H:H,-2,FALSE) <- in M2
=VLOOKUPneg(H2,Sheet2!H:H,-4,FALSE) <- in N2

while the first row has worked, the table has autofilled the rows under with H3, H4, H5, and aren't working
the below is the info im trying to pull across, both of the sheets are sorted in the same way

any ideas on what the problem could be or how i could fix it?

thanks in advance

 

[Alex Blakenburg]

Query tools are much better at lookups than Excel is and can easily do lookups on multiple columns with click and drag as opposed to sometimes quite complex formulas.

What you want to do is called Merge in Power query and the link (lookup) between tables is called a Join.
Here a couple of youtube videos.
1) Bill Jelen 6 mins
2) Jon Acampora 9.5 mins