hi,

Here is the idea behind the function I am trying to write:

"Knowing the width, height, length and weight, what is the price?"

----Box name---------Weight-----Box width---Box length---Box height----Price
0lbs-12in-12in-06in-------0----------12----------12----------06----------10
0lbs-12in-12in-12in-------0----------12----------12----------12----------20
0lbs-12in-18in-06in-------0----------12----------18----------06----------15
0lbs-12in-18in-12in-------0----------12----------18----------02----------25
5lbs-12in-12in-06in-------5----------12----------12----------06----------15
5lbs-12in-12in-12in-------5----------12----------12----------12----------25
5lbs-12in-18in-06in-------5----------12----------18----------06----------20
5lbs-12in-18in-12in-------5----------12----------18----------02----------30
---etc---


thanks :)



ps: This is on Excel 2003 - (11.820.8202) SP3

I would drop the leading 0 from your height category.

Then:

Lookup criteria:

...........A..........B
20...Weight.....0
21...Width......12
22...Length.....12
23...Height......6

=SUMPRODUCT(--(B2:B10=B20),--(C2:C10=B21),--(D2:D10=B22),--(E2:E10=B23),F2:F23)



--
Biff
Microsoft Excel MVP


"Antoine" wrote in message
...
hi,

Here is the idea behind the function I am trying to write:

"Knowing the width, height, length and weight, what is the price?"

----Box name---------Weight-----Box width---Box length---Box
height----Price
0lbs-12in-12in-06in-------0----------12----------12----------06----------10
0lbs-12in-12in-12in-------0----------12----------12----------12----------20
0lbs-12in-18in-06in-------0----------12----------18----------06----------15
0lbs-12in-18in-12in-------0----------12----------18----------02----------25
5lbs-12in-12in-06in-------5----------12----------12----------06----------15
5lbs-12in-12in-12in-------5----------12----------12----------12----------25
5lbs-12in-18in-06in-------5----------12----------18----------06----------20
5lbs-12in-18in-12in-------5----------12----------18----------02----------30
---etc---


thanks :)



ps: This is on Excel 2003 - (11.820.8202) SP3

Hi,

The data comes from another application that opens up Excel and populate a
bunch of cells.

Cells D6-D9 contains the data I work with.
These cells contains the width, height, length, weight of the content of a
box.
With your help, I have now categorized or formatted these cells properly
into cells K6-K9.


Below this is this table where the box name column is useless except for
training.

----Column C--------Column D----Column E----Column F----Column G----Column H
----Box name---------Weight-----Box width---Box length---Box height----Price

0lbs-12in-12in-06in-------0----------12----------12----------06----------10
0lbs-12in-12in-12in-------0----------12----------12----------12----------20
0lbs-12in-18in-06in-------0----------12----------18----------06----------15
0lbs-12in-18in-12in-------0----------12----------18----------02----------25
5lbs-12in-12in-06in-------5----------12----------12----------06----------15
5lbs-12in-12in-12in-------5----------12----------12----------12----------25
5lbs-12in-18in-06in-------5----------12----------18----------06----------20
5lbs-12in-18in-12in-------5----------12----------18----------02----------30
---etc---

Note: The actual data does not have "0" in front of the numbers: these are
for alignment in this textbox only.

So using nested if statement, I formatted the dimensions of the contents of
the box to the dimension of the actual box that will ship it.

Now I need to get the price of a box given the dimension located in cell
K6-K9 (which have been formatted to the available box dimension seen here in
this table).

The problem is that VLOOKUP looks up only one column and I need to match the
data of four cells to the data of four cells located in one row to get the
price given in the last column.

Can I do that using nested VLOOPUP functions?

Thank you very much for your input.
Reply With Quote

"T. Valko" wrote:

I would drop the leading 0 from your height category.

Then:

Lookup criteria:

...........A..........B
20...Weight.....0
21...Width......12
22...Length.....12
23...Height......6

=SUMPRODUCT(--(B2:B10=B20),--(C2:C10=B21),--(D2:D10=B22),--(E2:E10=B23),F2:F23)



--
Biff
Microsoft Excel MVP


"Antoine" wrote in message
...
hi,

Here is the idea behind the function I am trying to write:

"Knowing the width, height, length and weight, what is the price?"

----Box name---------Weight-----Box width---Box length---Box
height----Price
0lbs-12in-12in-06in-------0----------12----------12----------06----------10
0lbs-12in-12in-12in-------0----------12----------12----------12----------20
0lbs-12in-18in-06in-------0----------12----------18----------06----------15
0lbs-12in-18in-12in-------0----------12----------18----------02----------25
5lbs-12in-12in-06in-------5----------12----------12----------06----------15
5lbs-12in-12in-12in-------5----------12----------12----------12----------25
5lbs-12in-18in-06in-------5----------12----------18----------06----------20
5lbs-12in-18in-12in-------5----------12----------18----------02----------30
---etc---


thanks :)



ps: This is on Excel 2003 - (11.820.8202) SP3

Can I do that using nested VLOOPUP functions?

No. Did you try the formula I suggested? It works!

If you'd like I can post a sample file that demonstrates this.


--
Biff
Microsoft Excel MVP


"Antoine" wrote in message
...
Hi,

The data comes from another application that opens up Excel and populate a
bunch of cells.

Cells D6-D9 contains the data I work with.
These cells contains the width, height, length, weight of the content of a
box.
With your help, I have now categorized or formatted these cells properly
into cells K6-K9.


Below this is this table where the box name column is useless except for
training.

----Column C--------Column D----Column E----Column F----Column G----Column
H
----Box name---------Weight-----Box width---Box length---Box
height----Price

0lbs-12in-12in-06in-------0----------12----------12----------06----------10
0lbs-12in-12in-12in-------0----------12----------12----------12----------20
0lbs-12in-18in-06in-------0----------12----------18----------06----------15
0lbs-12in-18in-12in-------0----------12----------18----------02----------25
5lbs-12in-12in-06in-------5----------12----------12----------06----------15
5lbs-12in-12in-12in-------5----------12----------12----------12----------25
5lbs-12in-18in-06in-------5----------12----------18----------06----------20
5lbs-12in-18in-12in-------5----------12----------18----------02----------30
---etc---

Note: The actual data does not have "0" in front of the numbers: these are
for alignment in this textbox only.

So using nested if statement, I formatted the dimensions of the contents
of
the box to the dimension of the actual box that will ship it.

Now I need to get the price of a box given the dimension located in cell
K6-K9 (which have been formatted to the available box dimension seen here
in
this table).

The problem is that VLOOKUP looks up only one column and I need to match
the
data of four cells to the data of four cells located in one row to get the
price given in the last column.

Can I do that using nested VLOOPUP functions?

Thank you very much for your input.
Reply With Quote

"T. Valko" wrote:

I would drop the leading 0 from your height category.

Then:

Lookup criteria:

...........A..........B
20...Weight.....0
21...Width......12
22...Length.....12
23...Height......6

=SUMPRODUCT(--(B2:B10=B20),--(C2:C10=B21),--(D2:D10=B22),--(E2:E10=B23),F2:F23)



--
Biff
Microsoft Excel MVP


"Antoine" wrote in message
...
hi,

Here is the idea behind the function I am trying to write:

"Knowing the width, height, length and weight, what is the price?"

----Box name---------Weight-----Box width---Box length---Box
height----Price
0lbs-12in-12in-06in-------0----------12----------12----------06----------10
0lbs-12in-12in-12in-------0----------12----------12----------12----------20
0lbs-12in-18in-06in-------0----------12----------18----------06----------15
0lbs-12in-18in-12in-------0----------12----------18----------02----------25
5lbs-12in-12in-06in-------5----------12----------12----------06----------15
5lbs-12in-12in-12in-------5----------12----------12----------12----------25
5lbs-12in-18in-06in-------5----------12----------18----------06----------20
5lbs-12in-18in-12in-------5----------12----------18----------02----------30
---etc---


thanks :)



ps: This is on Excel 2003 - (11.820.8202) SP3

Hi!

Thank you for your help.
I think I understand your workaround.
Its like if I created a uniqueID for each row and did a vlookup on it right?

Thank you.

Antoine

Hi Biff,

Here is the file for your convenience. It is located on our poor man's ftp.
www.gmail.com
user: jacquesfiles
pass: !@#$%qwert

I am not sure I understand your code though.

=SUMPRODUCT(--(B2:B10=B20),--(C2:C10=B21),--(D2:D10=B22),--(E2:E10=B23),F2:F23)


thank you, really, for helping out.

Antoine & Jacques

"T. Valko" wrote:

I would drop the leading 0 from your height category.

Then:

Lookup criteria:

...........A..........B
20...Weight.....0
21...Width......12
22...Length.....12
23...Height......6

=SUMPRODUCT(--(B2:B10=B20),--(C2:C10=B21),--(D2:D10=B22),--(E2:E10=B23),F2:F23)



--
Biff
Microsoft Excel MVP


"Antoine" wrote in message
...
hi,

Here is the idea behind the function I am trying to write:

"Knowing the width, height, length and weight, what is the price?"

----Box name---------Weight-----Box width---Box length---Box
height----Price
0lbs-12in-12in-06in-------0----------12----------12----------06----------10
0lbs-12in-12in-12in-------0----------12----------12----------12----------20
0lbs-12in-18in-06in-------0----------12----------18----------06----------15
0lbs-12in-18in-12in-------0----------12----------18----------02----------25
5lbs-12in-12in-06in-------5----------12----------12----------06----------15
5lbs-12in-12in-12in-------5----------12----------12----------12----------25
5lbs-12in-18in-06in-------5----------12----------18----------06----------20
5lbs-12in-18in-12in-------5----------12----------18----------02----------30
---etc---


thanks :)



ps: This is on Excel 2003 - (11.820.8202) SP3

Its like if I created a uniqueID for each row and
did a vlookup on it right?

Essentially, yes!

Here's the formula based on your file link:

=SUMPRODUCT(--(D24:D47=ShipWeight),--(E24:E47=ShipWidth),--(F24:F47=ShipLength),--(G24:G47=ShipHeight),H24:H47)


--
Biff
Microsoft Excel MVP


"Antoine" wrote in message
...
Hi!

Thank you for your help.
I think I understand your workaround.
Its like if I created a uniqueID for each row and did a vlookup on it
right?

Thank you.

Antoine

P.S.

You should immediately change your gmail password! You don't want everyone
that reads these forums to have access to your account.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Its like if I created a uniqueID for each row and
did a vlookup on it right?

Essentially, yes!

Here's the formula based on your file link:

=SUMPRODUCT(--(D24:D47=ShipWeight),--(E24:E47=ShipWidth),--(F24:F47=ShipLength),--(G24:G47=ShipHeight),H24:H47)


--
Biff
Microsoft Excel MVP


"Antoine" wrote in message
...
Hi!

Thank you for your help.
I think I understand your workaround.
Its like if I created a uniqueID for each row and did a vlookup on it
right?

Thank you.

Antoine