I'm having problems using VLOOKUP in a database file (see attached) and need help to figure out why it's not working for 2 out of 12 columns in my data table (ie columns J and K), and why it produces unexpected/undesired results in a 3rd column (ie column R).
Here's the background context:
I want to use an Excel file on my Macbook Air to track my financial transactions. See attached. I'm using a table on the Data Entry worksheet in that file to hold all my transaction data (date, amount, account, along with add'l attributes for categorization and reporting like what account is involved, what the transaction's general expense category is, what it's specific expense type is, etc.), with 1 row of data for each transaction (generally speaking).
I created a set of drop-down lists for each transaction so I could enforce data validation when setting the attributes on each transaction. But, as soon as I started actually using the file, I realized that setting the setting 10+ attributes for each transaction using drop-down lists to select each attribute resulted in 20+ clicks per transaction. Too much manual work required for each transaction. So, I added a VLOOKUP table so that I could set 1 "profile" attribute for each Transaction, and that profile attribute selection would drive the setting of some/many/all the remaining attributes. This helped a lot.
But, for some reason, I've run into a problem: while my VLOOKUP formulas appear to work correctly and as expected to set attributes in 10 out of 12 columns of data in my data entry table, they won't work for 2 specific columns in the middle of my file (ie columns J & K). I can't figure that out. The exact same formula syntax appears to be working on all the other columns so I don't understand what it is about these 2 problematic columns that is blocking things. Additionally, my VLOOKUP formulas result in an unexpected/undesired result in a 3rd column that I'd also like help with (ie column R). Specifically, when the VLOOKUP table results in nothing needing to be set in this column, Excel is displaying a "0" in that cell instead of just leaving it blank (which is the desired outcome).
I'm hoping someone cal help me resolve this issue so that I can set the attributes in the 2 problematic columns in the exact same way I'm setting attributes in the other columns, and not have the weird results in the 3rd column. I suspect it's something quick and simple I'm just not seeing.
5 freelancers are bidding on average $21 for this job
Hello, i have 10 years experiments on excel and excel macro. My specialist area is solving problem and support users about excel. If you choose me, This will be my first job.