Excel table lookup help?

[pilotbob]

I’m trying to write a sheet that will return prices for custom sized panels that I manufacture. I have a basic price list in the form of a table with height running down the rows and width running along the columns. eg. B2=100mm, C2=200mm,D2=300mm (Width of panel)

A3=100mm, A4=200mm, A5=300mm (Height of panel)

I then have the price data entered into cells B3 to D5

In a manual paper based system if I want to get the price for a panel of say 162high x 224 wide I would look up the next size up in each dimension and use the value in the intersecting cell ie. Height is less than 200 Width is less than 300 so return the value at D4

I want to be able to enter the required size into say 2 adjacent cells (Height and Width) and have the price data returned to another adjacent cell. Ultimately this will be built into a much larger sheet to produce quotations and worksheets etc but I need the basic method for this first.

 

Any ideas as I’ve been at this now for a good few weeks without success?

 

Attached is my basic worksheet in a very simplified form as a starting point.

 

Many thanks.

I’m currently using Excel 2000.

[pilotbob]

I’m trying to write a sheet that will return prices for custom sized panels that I manufacture. I have a basic price list in the form of a table with height running down the rows and width running along the columns. eg. B2=100mm, C2=200mm,D2=300mm (Width of panel)

A3=100mm, A4=200mm, A5=300mm (Height of panel)

I then have the price data entered into cells B3 to D5

In a manual paper based system if I want to get the price for a panel of say 162high x 224 wide I would look up the next size up in each dimension and use the value in the intersecting cell ie. Height is less than 200 Width is less than 300 so return the value at D4

I want to be able to enter the required size into say 2 adjacent cells (Height and Width) and have the price data returned to another adjacent cell. Ultimately this will be built into a much larger sheet to produce quotations and worksheets etc but I need the basic method for this first.

 

Any ideas as I’ve been at this now for a good few weeks without success?

 

Attached is my basic worksheet in a very simplified form as a starting point.

 

Many thanks.

I’m currently using Excel 2000.

[pilotbob]

I’m trying to write a sheet that will return prices for custom sized panels that I manufacture. I have a basic price list in the form of a table with height running down the rows and width running along the columns. eg. B2=100mm, C2=200mm,D2=300mm (Width of panel)

A3=100mm, A4=200mm, A5=300mm (Height of panel)

I then have the price data entered into cells B3 to D5

In a manual paper based system if I want to get the price for a panel of say 162high x 224 wide I would look up the next size up in each dimension and use the value in the intersecting cell ie. Height is less than 200 Width is less than 300 so return the value at D4

I want to be able to enter the required size into say 2 adjacent cells (Height and Width) and have the price data returned to another adjacent cell. Ultimately this will be built into a much larger sheet to produce quotations and worksheets etc but I need the basic method for this first.

 

Any ideas as I’ve been at this now for a good few weeks without success?

 

Attached is my basic worksheet in a very simplified form as a starting point.

 

Many thanks.

I’m currently using Excel 2000.

Panel Prices.xls

[Guest Wolfeymole]

Hi Bob

 

This may be achievable via either =if arguments or input via list boxes, I'll work on it and get back to you.

[Guest Wolfeymole]

Hi Bob

 

This may be achievable via either =if arguments or input via list boxes, I'll work on it and get back to you.

[Guest Wolfeymole]

Hi Bob

 

This may be achievable via either =if arguments or input via list boxes, I'll work on it and get back to you.

[Guest Wolfeymole]

Right Bob I managed to throw something together which may help.

 

Have a look and see what you think of these 3 attempts.

 

The last in Formula view.

[Guest Wolfeymole]

Right Bob I managed to throw something together which may help.

 

Have a look and see what you think of these 3 attempts.

 

The last in Formula view.

[Guest Wolfeymole]

Right Bob I managed to throw something together which may help.

 

Have a look and see what you think of these 3 attempts.

 

The last in Formula view.

Panels.xls

Panels 1.xls

Panels 2.xls

[pilotbob]

OK, thanks for that so far, I have altered your Panels 1 file and attached as Panels 4 below. What I really need is a metohod of automatically extracting the reqired info from the price data. I've added a comment to cell E13 to illustrate what I'm looking for. I know it's a tough cookie but am sure it must be possible.

Thanks again, Bob.

[pilotbob]

OK, thanks for that so far, I have altered your Panels 1 file and attached as Panels 4 below. What I really need is a metohod of automatically extracting the reqired info from the price data. I've added a comment to cell E13 to illustrate what I'm looking for. I know it's a tough cookie but am sure it must be possible.

Thanks again, Bob.

[pilotbob]

OK, thanks for that so far, I have altered your Panels 1 file and attached as Panels 4 below. What I really need is a metohod of automatically extracting the reqired info from the price data. I've added a comment to cell E13 to illustrate what I'm looking for. I know it's a tough cookie but am sure it must be possible.

Thanks again, Bob.

Panels 4.xls

[pilotbob]

LOOKUP

 

As a further attempt at getting a soution to my other post can I get "LOOKUP" to return the next highest value in a range rather than the next lowest value.

See attached file. Many thanks again,

 

Bob.

[pilotbob]

LOOKUP

 

As a further attempt at getting a soution to my other post can I get "LOOKUP" to return the next highest value in a range rather than the next lowest value.

See attached file. Many thanks again,

 

Bob.

[pilotbob]

LOOKUP

 

As a further attempt at getting a soution to my other post can I get "LOOKUP" to return the next highest value in a range rather than the next lowest value.

See attached file. Many thanks again,

 

Bob.

LOOKUP.xls

[Guest Wolfeymole]

I can see what your after Bob but to me the obscure sizes are not relevant if you are going to charge for a set panel size.

 

To achieve what you want will mean a very complicated formula involving lots of =IF statements and => or <= statements.

[Guest Wolfeymole]

I can see what your after Bob but to me the obscure sizes are not relevant if you are going to charge for a set panel size.

 

To achieve what you want will mean a very complicated formula involving lots of =IF statements and => or <= statements.

[Guest Wolfeymole]

I can see what your after Bob but to me the obscure sizes are not relevant if you are going to charge for a set panel size.

 

To achieve what you want will mean a very complicated formula involving lots of =IF statements and => or <= statements.

[pilotbob]

OK, thanks for your input, I've actually sorted it now by using another lookup table to convert the actaul input dimensions to the next specific sizes. It's not very elegant but works just fine. Shame we couldn't find a better solution though but I'll keep working on it.

 

Regards,

 

Bob.

[pilotbob]

OK, thanks for your input, I've actually sorted it now by using another lookup table to convert the actaul input dimensions to the next specific sizes. It's not very elegant but works just fine. Shame we couldn't find a better solution though but I'll keep working on it.

 

Regards,

 

Bob.

[pilotbob]

OK, thanks for your input, I've actually sorted it now by using another lookup table to convert the actaul input dimensions to the next specific sizes. It's not very elegant but works just fine. Shame we couldn't find a better solution though but I'll keep working on it.

 

Regards,

 

Bob.