Forum Discussion
Solved
VLOOKUP - Need to Use "Number" as Text
I am creating a spreadsheet to track door access codes that have been programed and issued. The workbook includes 2 sheets:
Sheet 1: ISSUED
Sheet 2: CODES
In Sheet 1, I need to be able to select a door code from Sheet 2 (I am using a dropdown list in the appropriate column on Sheet 1), and populate some of the columns in the row on Sheet 1 with data from the table on Sheet 2.
The door codes are each 4 digit numerical codes. However, I have formatted the columns where the door code appear in both sheets as text and have ensured that each code entered into Sheet 2 is in fact stored as as text and not as a number.
The data I am attempting to display in the columns after selecting the door code from a dropdown is all text. The only column on either sheet that displays a "numerical value" are the door codes themselves.
Regardless, I continue to see the #N/A error on Sheet 1 when attempting to add my VLOOKUP formula.
How can I convince VLOOKUP to view my numerical door codes as text and return results for exact matches (which again, are selected from a dropdown menu) without this error?
Thank you!
Thanks. The problem is that VLOOKUP always searches the first column of the lookup range. But the codes are in the second column of the table.
If you have Microsoft 365 or Office 2021, you can use XLOOKUP:
In E2: =XLOOKUP([@CODE],Table13[CODE],Table13[DESCRIPTION],"")
In G2: =XLOOKUP([@CODE],Table13[CODE],Table13[STATUS],"")
If you have an older version, use INDEX/MATCH:
In E2: =IFERROR(INDEX(Table13[DESCRIPTION],MATCH([@CODE],Table13[CODE],0)),"")
In G2: =IFERROR(INDEX(Table13[STATUS],MATCH([@CODE],Table13[CODE],0)),"")
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
HansVogelaar Certainly!
I have uploaded a sample version of the spreadsheet to dropbox here: https://www.dropbox.com/scl/fi/d2ax6rc4d4ag9el7hwkuv/Access-Code-Tracker-Sample.xlsx?dl=0&rlkey=dvao4t8hnkx3gyo75gvblf1m7
Additionally, here is a breakdown of the logic I am following:
SHEET 1: ISSUED
A: will be manually populated
B: will be manually populated
C: will be manually populated
😧 will be manually populated
E: =VLOOKUP(F2,Table13,2,FALSE) - needs to pull data from corresponding row in column B on Sheet 2: CODES
F: drop-down selection from list on Sheet 2: CODES / column C
G: =VLOOKUP(F4,Table13,4,FALSE) - needs to pull data from corresponding row in column D on Sheet 2: CODES
SHEET 2: CODES
A: will be manually populated
B: will be manually populated
C: will be manually populated (numerical keypad code entered as text value)
😧 will be manually populatedThanks. The problem is that VLOOKUP always searches the first column of the lookup range. But the codes are in the second column of the table.
If you have Microsoft 365 or Office 2021, you can use XLOOKUP:
In E2: =XLOOKUP([@CODE],Table13[CODE],Table13[DESCRIPTION],"")
In G2: =XLOOKUP([@CODE],Table13[CODE],Table13[STATUS],"")
If you have an older version, use INDEX/MATCH:
In E2: =IFERROR(INDEX(Table13[DESCRIPTION],MATCH([@CODE],Table13[CODE],0)),"")
In G2: =IFERROR(INDEX(Table13[STATUS],MATCH([@CODE],Table13[CODE],0)),"")
