I think VLookup will work fine.

Let us assume that the product  table is a named range  called "tblProductList"
on a worksheet called "Products"

then use:

HighPrice = WorksheeetFunction.VLookup(ActiveWorksheet.range("B" &
Target.Row), Worksheets("Products").Range("tblProductlist"), 4, false)

LowPrice = WorksheeetFunction.VLookup(ActiveWorksheet.range("B" & Target.Row),
Worksheets("Products").Range("tblProductlist"), 3, false)

If Target.Value>LowPrice and Target.Value<HighPrice Then

'Do something with it

End If

(The long lines will undoubtedly get wrapped and you will need to unwrap them.
I have left balnk line between each line of code.)