Forum Discussion
DARKNHT476
Feb 03, 2025Copper Contributor
Excel, Data Validation and VLOOKUP use request
Hello,
I have a workbook with multiple tabs, but I have created a mock workbook linked below with only the two tabs in reference, and minimize the data to the columns needed on each tab. Long story short, in the actual workbook (confidential as to why I cant share it) I currently have a, INDEX formula used in COL D, and MID formula in COL E, and a DATA VALIDATION LIST in COL F on the "Data_Transformed" TAB.
What I am asking here is if I can put a VLOOKUP into COL F as well that will pull COL C data from the UNSPSC tab, and populate it into COL F on the Data_Transformed tab.
In a nutshell I have to manually enter data into COL's D, E, and F for 144K rows of data. So I made the above mentioned formulas' in COL D & E to reduce cell entries. I have a decent master UNSPSC code list, that I have predetermined a "MATERIAL GROUP" code I was to always use. So I would like to return that data from COL C on the UNSPSC tab into COL F on the Data_Transformed tab, so I can reduce the number of rows that need manual entry when we already have determined a Material Group for the UNSPSC Code associated with the UNSPSC Classification and Code. Then I can filter to blanks only for the manual entry needs.
For test purposes I only put a Material Group in COL C for 1 UNSPSC code/class to minimize the data shared here.
If you want to be able to enter values that aren't in the drop-down list, clear the check box 'Show error alert after invalid data is entered' in the Error Alert tab of the Data Validation dialog, or change the error style to Information instead of Stop.
Formula in F4:
=XLOOKUP(G4, 'UNSPSC Data'!A:A, 'UNSPSC Data'!C:C, "")
or
=IFERROR(VLOOKUP(G4, 'UNSPSC Data'!A:C, 2, FALSE), "")
- DARKNHT476Copper Contributor
That worked, thanks again!
If you want to be able to enter values that aren't in the drop-down list, clear the check box 'Show error alert after invalid data is entered' in the Error Alert tab of the Data Validation dialog, or change the error style to Information instead of Stop.
Formula in F4:
=XLOOKUP(G4, 'UNSPSC Data'!A:A, 'UNSPSC Data'!C:C, "")
or
=IFERROR(VLOOKUP(G4, 'UNSPSC Data'!A:C, 2, FALSE), "")
- DARKNHT476Copper Contributor
Hans, I am returning a 0 in the actual sheet in COL F on the test sheet on some. Which then flags a #N/A in COL D, is there a way I can have it just return a blank rather than a 0? IF so how would I modify the formula?
Please attach a copy of the workbook demonstrating the problem.
- DARKNHT476Copper Contributor
Hans, I have some cells in my MATERIAL GROUP COL (COL F on the demo) returning a 0, which flags a #N/A in my MATERIAL TYPE COL D in the example. Can I modify it to just return a blank rather than a zero? If so, how would I do that?
- DARKNHT476Copper Contributor
Hans thank you a ton! The XLOOKUP worked for me!