Forum Discussion
Excel, Data Validation and VLOOKUP use request
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), "")
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?