Price List look-up function

Hello,

I am not sure if I can do this without VBA; considering I have not used VBA in about a year, I'd rather exhaust my options before going back into it (as I know this might involve a lot of headaches).

In short, I'm trying to develop a faster way to compare the price of various items. The current process first involves looking at a sheet with a list of various measurements. Each measurement is then grouped to a particular code (5, 10, etc.):

As an example, I am looking for the price of something with measurements 1.083.

Example of measurement list:

Size	5S	10S	20S	STD
1"	.065	0.083	.133	.25
2"	.065	.109	.155	.260

Then, I compare the code and size to a price list to find the matching price:

Size	5S	10S	20S	STD
1"	$20	$25	$30	$40
2"	$22	$32	$42	$52

I would like to make this a lot faster by simply typing the sizes into a cell (or over two cells) and getting the matching price --> cross referencing the paper documents is very time consuming.
As an example, the bold/black is what I fill in; the red/itallic is what Excel pulls:
Over 1 cell

Size	Code	Price
1.083	10S	$25

Over 2 cells

Size	Size2	Code	Price
1"	.083	10S	$25

How can I do this?

Thank you,

Citizen

Are they all in the same sheet - different sheets - different books?

They are on the same sheet right now. If easier, I I'd move them to a different worksheet. Ideally, I'd like to keep everything in the same workbook.

Hi,

Does the attached help?

For this code you'll need to define the ranges that correspond to your actual sheet (marked in red):

Private Sub Worksheet_Change(ByVal Target As Range): Dim c As Long, r As Long
Dim Size As String, Code As String, Dec As String, Price As Single
Dim Plst As Range: Set Plst = Range("I16:M18")
Dim Dcml As Range: Set Dcml = Range("C16:G18")
Dim Rslt As Range: Set Rslt = Range("E21:G23")
If Not Intersect(Target, Rslt) Is Nothing Then
Application.EnableEvents = False
Size = Left(Target, 1) & Chr(34): Dec = Right(Target, 4)
If Dec > 1 Then Dec = Right(Target, 3)
c = Dcml.Find(Dec).Column: Code = Cells(Dcml.row, c)
c = Plst.Find(Code).Column: r = Plst.Find(Size).row
Price = Cells(r, c)
Target.Offset(0, 1) = Code: Target.Offset(0, 2) = Price
End If
Application.EnableEvents = True
End Sub

I had no trouble uploading Mr. Buttrey's attachment - have you tried my code?

Here is an example of the schedule I'm trying to apply the above to, it's just not pulling accurately and I do not know how to fix it.

What makes the function more difficult is the additional information that I did not illustrate in my original post:
1. >size=12, there is an "if" E or I.
2. Pricing is only available for 7 codes, which means the there is a range/between criterion to qualify a code

Probably the simplest way is to ensure that the Price sheet is consistent and has an I and E price row for every size, (so sizes 2,3,4,6,8,10 & 12 would have zero value rows).

Then just modify the formula in F6 on the D! sheet with an if test. i.e.

Formula:

=IF(D6="I",INDEX(PRICE!$A$2:$I$35,D!$B$1,D!$C$1),INDEX(PRICE!$A$2:$I$35,D!$B$1+1,D!$C$1))