In real work the lookup table almost never sits on the same sheet as your report. The formula is the same as usual. The only new part is how you point to the other sheet.
VLOOKUP to another sheet
Say your prices are on a sheet called Prices in A:B:
=VLOOKUP(A2, Prices!A:B, 2, FALSE)
Prices! tells Excel which sheet. If the sheet name has a space, wrap it in quotes: 'Price List'!A:B.
The easy way to write it
Type =VLOOKUP(A2, then click the other sheet tab and select the range with the mouse. Excel writes the sheet reference for you.
XLOOKUP across sheets
=XLOOKUP(A2, Prices!A:A, Prices!B:B, "Not listed")
XLOOKUP needs the lookup column and the result column separately, so you never count columns.
Another workbook
The formula looks like =VLOOKUP(A2, [Prices.xlsx]Sheet1!$A:$B, 2, FALSE). It works while both files are open. If the other file is closed, Excel shows the full path and the formula keeps working, but only if the file is not moved or renamed.
If you get #N/A
- Check for extra spaces with TRIM.
- Check that numbers are not stored as text.
- Check you used FALSE for an exact match.
Tip: use a Table or named range
If you turn the lookup data into a Table, the formula can use the table name and grows automatically when new rows are added.