Vlookup multiples and round up
 

I have a Poster wrksheet that has the heigt and width of all the posters. The
height is multiplied by the width to get the total square inches. I have
another wrksheet that has a table that includes a range of square inches and
then a price for each instance. For certain square inch totals there is more
than one possible price. On the poster wrksheet I want to lookup the square
inces for a poster on the price wrksheet and return all possible prices for
that size. Also, I don't want to display them all in one column but rather in
one row and I need the formula to round up to the next highest square inch
value in the price table if it is between two values.

e.g.

PRICE TABLE

Total Sq. Inches Price
92 2.00
100 2.10
135 2.50
145 3.00
145 3.20

POSTER TABLE

Poster Height Width Square Inches Price 1 Price 2
Price 3
D12RS 12 12 144 3.00 3.25
D45PP 10 10 100 2.10

Thanks for your help.

Vlookup multiples and round up
 

I would add another column to the poster table that calculates the next
higher size if an exact size is not listed in the price table. Trust me,
this will make things much easier!

Here's a small sample file that demonstrates this:

poster_size_lookup.xls 14kb

http://cjoint.com/?bdeFaw26cM

The formulas (highlighted in yellow) in columns E:H are array formulas. They
need to be entered using the key combination of CTRL,SHIFT,ENTER (not just
ENTER). If you edit them they need to be re-entered as arrays using the key
combo.

Biff

"Spencer" wrote in message
...
I have a Poster wrksheet that has the heigt and width of all the posters.
The
height is multiplied by the width to get the total square inches. I have
another wrksheet that has a table that includes a range of square inches
and
then a price for each instance. For certain square inch totals there is
more
than one possible price. On the poster wrksheet I want to lookup the
square
inces for a poster on the price wrksheet and return all possible prices
for
that size. Also, I don't want to display them all in one column but rather
in
one row and I need the formula to round up to the next highest square inch
value in the price table if it is between two values.

e.g.

PRICE TABLE

Total Sq. Inches Price
92 2.00
100 2.10
135 2.50
145 3.00
145 3.20

POSTER TABLE

Poster Height Width Square Inches Price 1 Price 2
Price 3
D12RS 12 12 144 3.00
3.25
D45PP 10 10 100 2.10

Thanks for your help.

Vlookup multiples and round up
 

Spencer I needed to insert a column to help with the rounded area in column E

The table was in A1:B6 with A1:B1=Headings

The data rows was are
[Row 13]=D12RS
[Row 14]=D45PP

In D13:
=IF($D13MAX($A$2:$A$6),"Error",
OFFSET($A$1,MATCH($D13,$A$2:$A$6,-1),0))

This is a normal function. It returns 'Error' if the number is larger than
the largest number in the list.

In E13 (array formula Shft+Ctrl+Enter:
=IF(ISERROR(INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=$E1 3,ROW($A$1:$A$6)),ROW($1:$1)),2)),"",INDEX($A$1:$B $6,SMALL(IF($A$1:$A$6=$E13,ROW($A$1:$A$6)),ROW($1: $1)),2))

In F13 (array formula Shft+Ctrl+Enter:
=IF(ISERROR(INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=$E1 3,ROW($A$1:$A$6)),ROW($2:$2)),2)),"",INDEX($A$1:$B $6,SMALL(
IF($A$1:$A$6=$E13,ROW($A$1:$A$6)),ROW($2:$2)),2))

In G13 (array formula Shft+Ctrl+Enter:
=IF(ISERROR(INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=$E1 3,ROW($A$1:$A$6)),ROW($3:$3)),2)),"",INDEX($A$1:$B $6,SMALL(
IF($A$1:$A$6=$E13,ROW($A$1:$A$6)),ROW($3:$3)),2))

If you have more prices then you need to change the row($n:$n) for an
increment

Then copy them down.

Hope it works.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Spencer" wrote:

I have a Poster wrksheet that has the heigt and width of all the posters. The
height is multiplied by the width to get the total square inches. I have
another wrksheet that has a table that includes a range of square inches and
then a price for each instance. For certain square inch totals there is more
than one possible price. On the poster wrksheet I want to lookup the square
inces for a poster on the price wrksheet and return all possible prices for
that size. Also, I don't want to display them all in one column but rather in
one row and I need the formula to round up to the next highest square inch
value in the price table if it is between two values.

e.g.

PRICE TABLE

Total Sq. Inches Price
92 2.00
100 2.10
135 2.50
145 3.00
145 3.20

POSTER TABLE

Poster Height Width Square Inches Price 1 Price 2
Price 3
D12RS 12 12 144 3.00 3.25
D45PP 10 10 100 2.10

Thanks for your help.

Vlookup multiples and round up
 

=IF($D13MAX($A$2:$A$6),"Error",
OFFSET($A$1,MATCH($D13,$A$2:$A$6,-1),0))

Using a match_type of -1 requires the table be sorted in descending order.

Biff

"Martin Fishlock" wrote in message
...
Spencer I needed to insert a column to help with the rounded area in
column E

The table was in A1:B6 with A1:B1=Headings

The data rows was are
[Row 13]=D12RS
[Row 14]=D45PP

In D13:
=IF($D13MAX($A$2:$A$6),"Error",
OFFSET($A$1,MATCH($D13,$A$2:$A$6,-1),0))

This is a normal function. It returns 'Error' if the number is larger than
the largest number in the list.

In E13 (array formula Shft+Ctrl+Enter:
=IF(ISERROR(INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=$E1 3,ROW($A$1:$A$6)),ROW($1:$1)),2)),"",INDEX($A$1:$B $6,SMALL(IF($A$1:$A$6=$E13,ROW($A$1:$A$6)),ROW($1: $1)),2))

In F13 (array formula Shft+Ctrl+Enter:
=IF(ISERROR(INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=$E1 3,ROW($A$1:$A$6)),ROW($2:$2)),2)),"",INDEX($A$1:$B $6,SMALL(
IF($A$1:$A$6=$E13,ROW($A$1:$A$6)),ROW($2:$2)),2))

In G13 (array formula Shft+Ctrl+Enter:
=IF(ISERROR(INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=$E1 3,ROW($A$1:$A$6)),ROW($3:$3)),2)),"",INDEX($A$1:$B $6,SMALL(
IF($A$1:$A$6=$E13,ROW($A$1:$A$6)),ROW($3:$3)),2))

If you have more prices then you need to change the row($n:$n) for an
increment

Then copy them down.

Hope it works.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Spencer" wrote:

I have a Poster wrksheet that has the heigt and width of all the posters.
The
height is multiplied by the width to get the total square inches. I have
another wrksheet that has a table that includes a range of square inches
and
then a price for each instance. For certain square inch totals there is
more
than one possible price. On the poster wrksheet I want to lookup the
square
inces for a poster on the price wrksheet and return all possible prices
for
that size. Also, I don't want to display them all in one column but
rather in
one row and I need the formula to round up to the next highest square
inch
value in the price table if it is between two values.

e.g.

PRICE TABLE

Total Sq. Inches Price
92 2.00
100 2.10
135 2.50
145 3.00
145 3.20

POSTER TABLE

Poster Height Width Square Inches Price 1 Price 2
Price 3
D12RS 12 12 144 3.00
3.25
D45PP 10 10 100 2.10

Thanks for your help.

Vlookup multiples and round up
 

Biff: Thanks for spotting my omission.

Spenser: Biff is quite correct, I forgot to mention that the data table
needs to be sorted in decending order on the total sq. inches but on
ascending order for the price.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"T. Valko" wrote:

=IF($D13MAX($A$2:$A$6),"Error",
OFFSET($A$1,MATCH($D13,$A$2:$A$6,-1),0))

Using a match_type of -1 requires the table be sorted in descending order.

Biff

"Martin Fishlock" wrote in message
...
Spencer I needed to insert a column to help with the rounded area in
column E

The table was in A1:B6 with A1:B1=Headings

The data rows was are
[Row 13]=D12RS
[Row 14]=D45PP

In D13:
=IF($D13MAX($A$2:$A$6),"Error",
OFFSET($A$1,MATCH($D13,$A$2:$A$6,-1),0))

This is a normal function. It returns 'Error' if the number is larger than
the largest number in the list.

In E13 (array formula Shft+Ctrl+Enter:
=IF(ISERROR(INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=$E1 3,ROW($A$1:$A$6)),ROW($1:$1)),2)),"",INDEX($A$1:$B $6,SMALL(IF($A$1:$A$6=$E13,ROW($A$1:$A$6)),ROW($1: $1)),2))

In F13 (array formula Shft+Ctrl+Enter:
=IF(ISERROR(INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=$E1 3,ROW($A$1:$A$6)),ROW($2:$2)),2)),"",INDEX($A$1:$B $6,SMALL(
IF($A$1:$A$6=$E13,ROW($A$1:$A$6)),ROW($2:$2)),2))

In G13 (array formula Shft+Ctrl+Enter:
=IF(ISERROR(INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=$E1 3,ROW($A$1:$A$6)),ROW($3:$3)),2)),"",INDEX($A$1:$B $6,SMALL(
IF($A$1:$A$6=$E13,ROW($A$1:$A$6)),ROW($3:$3)),2))

If you have more prices then you need to change the row($n:$n) for an
increment

Then copy them down.

Hope it works.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Spencer" wrote:

I have a Poster wrksheet that has the heigt and width of all the posters.
The
height is multiplied by the width to get the total square inches. I have
another wrksheet that has a table that includes a range of square inches
and
then a price for each instance. For certain square inch totals there is
more
than one possible price. On the poster wrksheet I want to lookup the
square
inces for a poster on the price wrksheet and return all possible prices
for
that size. Also, I don't want to display them all in one column but
rather in
one row and I need the formula to round up to the next highest square
inch
value in the price table if it is between two values.

e.g.

PRICE TABLE

Total Sq. Inches Price
92 2.00
100 2.10
135 2.50
145 3.00
145 3.20

POSTER TABLE

Poster Height Width Square Inches Price 1 Price 2
Price 3
D12RS 12 12 144 3.00
3.25
D45PP 10 10 100 2.10

Thanks for your help.