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 st...

excel

Formulas and Functions

office 365

HansVogelaar
Feb 03, 2025
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), "")

HansVogelaar

MVP

Feb 03, 2025

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

=IFERROR(VLOOKUP(G4, 'UNSPSC Data'!A:C, 2, FALSE), "")

DARKNHT476

Copper Contributor

Feb 03, 2025

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

Forum Discussion

Excel, Data Validation and VLOOKUP use request

Resources