If there is a list of products, etc., it is possible to enter them by means of a pull-down list, from which the necessary items can be retrieved to avoid input errors.
Here we will show you how to link the VLOOKUP function with a drop-down list of Data Validation.
The same procedure can be used with the XLOOKUP function.
Steps
Prepare a worksheet like the one below as a sample.
Next, prepare a sheet for searching.
In this sheet, when you select a product, you will automatically get the price.
Set Data Validation(pull-down).
Select the range of cells for which you want to set the pull-down and choose Data Validation.
Change the "Allow" from "Any value" to "List".
Then press the "↑" button in the "Source" input column to select a range of cells.
This allows for pull-down entry.
Set function.
Next, create a formula to search.
For the VLOOKUP function.
=VLOOKUP(Cell of the pull-down, Cell range of the list. Absolute reference, Column number starting from 1,FALSE)=VLOOKUP(B3,List!$B$3:$C$6,2,FALSE)
For the XLOOKUP function.
=XLOOKUP(Cell of the pull-down, Cell range to search. Absolute reference, Resulting cell range. Absolute reference)=XLOOKUP(B3,List!B$3:$B$6,List!$C$3:$C$6)
Once the formula is complete, copy the rows you have created as many times as you need, and you are done.
In the case of the XLOOKUP function, it is also useful to make it a Spill. In this case, it need not be an absolute reference.
=XLOOKUP(Cell of the pull-down, Cell range to search, Resulting cell range)=XLOOKUP(B3:B6,List!B3:B6,List!C3:C6)
---