Forum Discussion
VLOOKUP - Need to Use "Number" as Text
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 populated
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)),"")
- Worked like a charm! Thank you so much for your time!