Forum Discussion

DARKNHT476's avatar
DARKNHT476
Copper Contributor
Feb 03, 2025

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. 

 

Excel Test Help MRO Codes.xlsx

  • 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), "")

  • 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), "")

    • DARKNHT476's avatar
      DARKNHT476
      Copper 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?

       

       

    • DARKNHT476's avatar
      DARKNHT476
      Copper 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?

    • DARKNHT476's avatar
      DARKNHT476
      Copper Contributor

      Hans thank you a ton! The XLOOKUP worked for me! 

Resources