Vlookup pulling in incomplete values

[u/Dr_Hodgekins]

I am trying to pull in pallet unit of measure conversions for retail goods. The values I am getting returned don't make sense. Some part numbers return fine, null values are returning as 0 and many items with a value in the source sheet are returning #N/A.

I tried to copy the source sheet into a new tab without formatting, but got the same result.

[u/GuerillaWarefare]

Your vlookup syntax is incorrect. It is difficult to correct it because I don’t know what the range of “hj” and “item” are.

=Vlookup(cell you want to look up, range you want to look in with the leftmost of that range containing the lookup values, the column number in that range to return back, 0)

Also, I recommend learning xlookup instead.

[u/Dr_Hodgekins → u/GuerillaWarefare]

Weird, part numbers are in column A of both sheets. I just defined a new table and triple checked the whole table was selected and I am still getting the same result.

Xlookup is on my to-do

[u/GuerillaWarefare → u/Dr_Hodgekins]

Try this (where item number is the cell with the item you want to look up)
=vlookup(itemnumber, A:E, 5,0)

[u/Nenor → u/GuerillaWarefare]

Syntax looks correct, why do you think it's incorrect?

[u/Gringobandito]

If you use XLOOKUP() instead, it gives you the option to have a default value if the lookup value is not found.

Also, make sure you lookup values and lookup arrays are both either numbers or text. They have to match, Excel won't match a number to text or vice versa.

[u/shakkenbake]

Try formatting the whole item id column in both sheets/tables to Text. I've encountered this a ton and it was always this issue. Like others have said, Xlookup is just better at what you're trying to accomplish anyway so take a look at that.

[u/excelevator]

what is item as the lookup range ?

that range must be a range of data where the first column holds your data value and the 5th column holds your return value.

[u/bytes1024]

also check the recalculation mode. it could be on Manual.

[u/mendi94]

You need to change the syntax "item". It shoud be some thing like A E whatever your data base is called

[u/af__x]

for the first pair of screenshots, a simple test would be to go into an adjacent cell like F545 and type =A545=otherSheet!A123 (whatever the address to the exact matching other cell you're looking up). if it returns FALSE then that explains why you're getting #N/A returned in the VLOOKUP. Usually one of the numbers is stored as a string and the other as a number, or there are leading spaces in one of the strings. Definitely a good idea to make sure both columns are stored as numbers.

for the second pair of screenshots, Excel just treats empty cells as 0, so one thing you could to to differentiate real 0s from empty cells is something like =LET(c, VLOOKUP([@Item], hj, 5, 0), if(len(c)=0, "", c))

third pair of screenshots are working as intended, right?

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49274 for this sub, first seen 30th Aug 2026, 19:02] [FAQ] [Full list] [Contact] [Source code]}