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), "")
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.
Here you go
https://1drv.ms/x/c/58c4e46433b7a4f2/Ee7ufDbDITxGjmizPtEJq1kBJoPvUqIujZ-E2NgtUYG8Zw?e=A9EdLI
Thank you, but I don't see the problem in that workbook...
Apologies, I didn't pull the formula down. Now it's there. It returns a 0 in COL F, where I'd like to just return a blank if possible.