Nested VLOOKUP?
 

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

Nested VLOOKUP?
 

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

Nested VLOOKUP?
 

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

Nested VLOOKUP?
 

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

Nested VLOOKUP?
 

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

Nested VLOOKUP?
 

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

Nested VLOOKUP?
 

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

Nested VLOOKUP?
 

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