Log In
Register
Home
New Member
March 8, 2022
Offline
I am struggling with a persistent #N/A message.
Formula is this: =VLOOKUP(D6,'[Clients.xlsb]Jobs-Orders'!$A$3:$Y$10000,7,FALSE)
I have checked the following:
D6 refers to the correct cell for the lookup_value, and is formatted as number, in this case 333.
The table_array is in another workbook, called Clients, and the sheet is Jobs-Orders. Both are open as I struggle with this.
The table_array is from $A$3 to $Y$390, but I have made it to $Y$10000 to allow for data growth (Yes, I know that it could have been a Table, but this goes way back in my learning curve).
The matching entry for the lookup_value is in Col.A of the 'source' workbook, hence in the leftmost column, and it too is formatted as Number. And 333 is definitely there.
The value I want returned is in Col.G, IE #7 for col_index_num, so seems correct.
What is wrong?!
I've used vlookup in plenty of other applications, and they have generally been successful.
I have tried again with the following formula, copied and pasted from another sheet in the same workbook, where the vlookup is working OK, and bringing up the exact same required data. Grrrr!!!!
=VLOOKUP(B1,'\\DOMINIC-PC\Documents\user-files\Documents\SUNNVENT\Clients\[Clients.xlsb]Jobs-Orders'!$A$3:$Y$10000,7,FALSE). (I've changed the B1 to D6, to suit the different worksheet layouts).
Also to note, other vlookups on other sheets in this workbook are working OK, also referencing the Clients workbook.
(BTW, I have also posted this question ow Microsoft TechCommunity. I don't know what your policy is in this regard. Let me know if this is not OK).
Trusted Members
Moderators
November 1, 2018
Offline
The cell format being number does not necessarily mean that the data is actually stored as a number. From your description, either you have an actual mismatch in the data such as leading/trailing spaces in one or other value, or one is stored as text and the other as number. See if this works:
=IFERROR(VLOOKUP(TEXT(D6,"0"),'[Clients.xlsb]Jobs-Orders'!$A$3:$Y$10000,7,FALSE),VLOOKUP(D6+0,'[Clients.xlsb]Jobs-Orders'!$A$3:$Y$10000,7,FALSE))
VIP
Trusted Members
June 25, 2016
Offline
Another reason for the #N/A could be the displayed value and the actual value is different.
For example, the cell is formatted to display 333 but the actual stored value could be 333.01
1 Guest(s)
