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

 

[dave3009]

Why not just use INDEX/MATCH which is good to go for any Excel version.

=IFNA(INDEX(Sheet2!F:F,MATCH(H2,Sheet2!H:H,0)),"Not Found")

If you can be certain all users will have M365/Excel 2021/LTS:

=XLOOKUP(H2,Sheet2!H:H,Sheet2!F:F,"Not Found")

 

[nzt101]

Thanks for the reply Dave,
I was trying to work out the index/match, after too long away from excel its all greek to me again haha
Putting the code into a cell in serial column, it worked for that row, but as soon as i drag it down to populate the lower rows, it fills it with "Not Found"
when i check the formula in each of the below rows it has the correct cell number, H3, H4, H5 etc

=IFNA(INDEX(Sheet2!F:F,MATCH(H2,Sheet2!H:H,0)),"Not Found")

i just tried changing the H2 to H3 and it comes back with Not Found as well, could there be something wrong with how the data is arranged in Sheet2?

 

[dave3009]

Thanks for the reply Dave,
I was trying to work out the index/match, after too long away from excel its all greek to me again haha
Putting the code into a cell in serial column, it worked for that row, but as soon as i drag it down to populate the lower rows, it fills it with "Not Found"
when i check the formula in each of the below rows it has the correct cell number, H3, H4, H5 etc

View attachment 74730

=IFNA(INDEX(Sheet2!F:F,MATCH(H2,Sheet2!H:H,0)),"Not Found")

i just tried changing the H2 to H3 and it comes back with Not Found as well, could there be something wrong with how the data is arranged in Sheet2?

If it's returning "Not Found" then it's simply not found. There is some difference between H2 and Sheet2 col H. This could be numbers stored as text, or whitespace. The formula is fine though.

 

[Alex Blakenburg]

How have you populated the table you are looking up. The Batch column looks like a mix of Text and Numbers.
Try this in M2 and let it fill down and see if it works.

=Iferror(VLOOKUPneg(H2,Sheet2!H:H,-2,FALSE),VLOOKUPneg(Trim(H2),Sheet2!H:H,-2,FALSE))

 

[nzt101]

How have you populated the table you are looking up. The Batch column looks like a mix of Text and Numbers.
Try this in M2 and let it fill down and see if it works.

=Iferror(VLOOKUPneg(H2,Sheet2!H:H,-2,FALSE),VLOOKUPneg(Trim(H2),Sheet2!H:H,-2,FALSE))

Thanks Alex,
This one has worked perfectly, pulled down and it filled all the information for the matches it found.
Is there a way of putting "Not Found" into a cell that has been filled with #VALUE! because the H cell wasn't found on Sheet2?

Thanks also Dave, i've got something to work with now when i need to use index/match again

 

[Alex Blakenburg]

Put a second Iferror formula around the whole thing.

=iferror(Iferror(VLOOKUPneg(H2,Sheet2!H:H,-2,FALSE),VLOOKUPneg(Trim(H2),Sheet2!H:H,-2,FALSE)),"Not Found")

Ideally you want to sort out how you are getting the data and ensure it comes in consistent format ie all numbers or all text.

 

[nzt101]

Put a second Iferror formula around the whole thing.

=iferror(Iferror(VLOOKUPneg(H2,Sheet2!H:H,-2,FALSE),VLOOKUPneg(Trim(H2),Sheet2!H:H,-2,FALSE)),"Not Found")

Ideally you want to sort out how you are getting the data and ensure it comes in consistent format ie all numbers or all text.

Thank you,

the data is being exported from a database to an xlsx file, which i'm then loading and transforming to remove unwanted columns and rows.
i dont have any power over how its exported, is there a way in power query to do what the trim function does?

 

[Alex Blakenburg]

In power query you wouldn't need to. Just select the Data Type that works best and make sure it is the same in both tables.
eg Make that Column a Data Type of either Text or Whole Number. I tend to stick with the data type that it is in the underlying database.
If the underlying database has it as text and you choose number, it may be fine if there is a policy in place that it will always be a number but if someone decides hey I would really like an "A" at the beginning or end it will have a flow on affect to your model.

If both tables are from Power Query, you could do the lookup in the Query instead of doing it with formulas afterward.

PS: All the Trim is doing in this case is converting it to Text. Most people use something like H2 & "" to do that.
I just got in the habit of using Trim, I find it more intuitive and takes care of a multitude of sins when getting data from another source.

 

[nzt101]

If both tables are from Power Query, you could do the lookup in the Query instead of doing it with formulas afterward.

Yep both tables are from power query, seperate .xlsx files with data loaded from each, so i can match and import the extra columns into one sheet?
at the moment i have 2 sheets that i'm pulling information from one to fill the other, if power query can match and combine into one sheet that'd be awesome