Price List look-up function

[citizenzen]

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

[xladept]

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

[citizenzen]

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.

[Richard Buttrey]

Hi,

Does the attached help?

[xladept]

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

Please Login or Register  to view this content.

[citizenzen]

I'm not sure where I am going wrong when I apply the logic behind the above 2 methods to my template.

I would like to show the schedule, but I can't upload it. After trying to upload the excel attachment, I keep getting an error message with the upload.

[xladept]

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

[citizenzen]

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

[Richard Buttrey]

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.

Please Login or Register  to view this content.