vlookup - strange result

[ajm]

hi all, curious as to how vlookup does its thing. I am well versed in how it is supposed to work (lookup this xx, in that range, and return me the value i want from a specified column; use "false" for exact match.

vlookup(B2,$D$2:$f$500, 2,0)

now i have a list of business numbers on one sheet that have a sort order number adjacent to them. The business numbers are not in ascending order. On another tab, i have a another version of the original data to which i want to apply the same sort. so, i added a vlookup based on the business number, looking in the range B:F on my other tab. The sort order is in col E.

VLOOKUP(E3,'FY24 ABN EMAIL CONTACT'!$A$13:$B$1084,2,0) (first row of data on source sheet is 13)

this formula works as it should except where it comes to a handful business numbers. this formula for several different business numbers (6 different numbers) returns 41. the first of these 6 is actually 267th in the list.

 

[ajm]

filtered rows were killing me. still don't know why 6 returned the same answer but they all changed once i unfiltered the rows.

 

[RoryA]

they all changed once i unfiltered the rows

That would worry me greatly since VLOOKUP is not affected by filters...

 

[ajm]

That would worry me greatly since VLOOKUP is not affected by filters...

as it did me. still don't know what happened but have to assume i did something stupid somewhere. I have added it to my list of things to look into further when i get some spare time.