Forum Discussion
VLOOKUP HELP! Monster Formula not working.
I have a spreadsheet that was created for me for work by someone at my previous company. Since switching to a new company and trying to pull the same data and import it into this spreadsheet it is not reading it right in 1 cell.
It seems like the formula in the output tab with the VLOOKUP is looking for the exact wording as before, but my new software that I am pulling the data from is does not shoe the exact info. Like for example instead of saying primaryresidence it says primary and instead of conventional it says conv. Could there be something in the formula that is making it so it is not reading the information and converting it correctly if it is not saying exactly what it was before?
Here is the formula that is currently in there.
=CONCATENATE(VLOOKUP(RawDat[@[Loan Type]],LoanType,2,FALSE), " ", VLOOKUP(RawDat[@[Loan Purpose]], Purpose,2,FALSE), CHAR(10), VLOOKUP(RawDat[@[Loan Term]],Term,2,FALSE), " ",VLOOKUP(RawDat[@AmorType], Amort, 2, FALSE)," ", RawDat[@[Arm Amort Type Descr]], CHAR(10), VLOOKUP(RawDat[@[Occupancy (P/S/I)]], OccAb,2,FALSE))
I've attached the file to this thread. I am getting the #N/A in the output tab under loan type.
It's combining a few different cells from the RAW DATA tab and transposing them to a little bit different wording so I can use what was in the output tab.
Obviously I am no excel expert so any help would be great!
You are exactly right. The formula is looking for the exact phrase, and since your new system uses different wording, it can't find the info, and if one of the instances of VLOOKUP returns N/A, then the entire formula returns NA. This workbook has a lot of hidden sheets, and one of them is called Lookup Tables. If you right click on a tab, and select unhide, then select Lookup Tables, you will see 8 separate tables listed. In each table, the first column is what the formula is looking for, and the second column is what it would display. The first column should contain the values as they appear in your raw data, the second column, would be what is displayed in your output sheet. You will have to go through each table, and replace the values in the first columns with what your raw data displays now. Keep in mind, these are tables. You can tell by the filled in corner at the bottom right cell of each. If you need to add a new row to the table, select the bottom right cell, and press tab
3 Replies
You are exactly right. The formula is looking for the exact phrase, and since your new system uses different wording, it can't find the info, and if one of the instances of VLOOKUP returns N/A, then the entire formula returns NA. This workbook has a lot of hidden sheets, and one of them is called Lookup Tables. If you right click on a tab, and select unhide, then select Lookup Tables, you will see 8 separate tables listed. In each table, the first column is what the formula is looking for, and the second column is what it would display. The first column should contain the values as they appear in your raw data, the second column, would be what is displayed in your output sheet. You will have to go through each table, and replace the values in the first columns with what your raw data displays now. Keep in mind, these are tables. You can tell by the filled in corner at the bottom right cell of each. If you need to add a new row to the table, select the bottom right cell, and press tab
Bob,
Thank you so much! I fixed the tables and they work perfect now.
I really appreciate the assistance with this!
Awesome! That's great to hear. I'm glad I was able to assist.
