Table Lookup using two data elements
 

Hello,

I would like to be able to look up a price depending on both color & count
of items.
My table looks like:

# red blue green black
1-5 .20 .30 .31 .32
6-20 .15 .26 .28 .28

Such that if I ordered 18 blue pens the value returned would be (.26)
It seems that I need a combination of both V & H Lookup.

Table Lookup using two data elements
 

Not sure how many quantity ranges you have, but with your current set up, you
might need to convert a discrete quantity into a range value. Once you do
that though, you can use a MATCH function on the color to drive the column
value in the VLOOKUP pretty easily.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"M.O.R." wrote:

Hello,

I would like to be able to look up a price depending on both color & count
of items.
My table looks like:

# red blue green black
1-5 .20 .30 .31 .32
6-20 .15 .26 .28 .28

Such that if I ordered 18 blue pens the value returned would be (.26)
It seems that I need a combination of both V & H Lookup.

Table Lookup using two data elements
 

Try this:

=INDEX(A1:E3,MATCH(18,INDEX(--LEFT(A1:A3),)),MATCH("blue",A1:E1,))


"M.O.R." wrote:

Hello,

I would like to be able to look up a price depending on both color & count
of items.
My table looks like:

# red blue green black
1-5 .20 .30 .31 .32
6-20 .15 .26 .28 .28

Such that if I ordered 18 blue pens the value returned would be (.26)
It seems that I need a combination of both V & H Lookup.

Table Lookup using two data elements
 

That will find a match on the color but will not return the correct value.
The value returned for "blue" is dependent upon how many are ordered. If 1-5
are ordered then the valule should be (.30) and if 6-20 are ordered then the
value should be (.26)

"Teethless mama" wrote:

Try this:

=INDEX(A1:E3,MATCH(18,INDEX(--LEFT(A1:A3),)),MATCH("blue",A1:E1,))


"M.O.R." wrote:

Hello,

I would like to be able to look up a price depending on both color & count
of items.
My table looks like:

# red blue green black
1-5 .20 .30 .31 .32
6-20 .15 .26 .28 .28

Such that if I ordered 18 blue pens the value returned would be (.26)
It seems that I need a combination of both V & H Lookup.

Table Lookup using two data elements
 

Works on my machine

"M.O.R." wrote:

That will find a match on the color but will not return the correct value.
The value returned for "blue" is dependent upon how many are ordered. If 1-5
are ordered then the valule should be (.30) and if 6-20 are ordered then the
value should be (.26)

"Teethless mama" wrote:

Try this:

=INDEX(A1:E3,MATCH(18,INDEX(--LEFT(A1:A3),)),MATCH("blue",A1:E1,))


"M.O.R." wrote:

Hello,

I would like to be able to look up a price depending on both color & count
of items.
My table looks like:

# red blue green black
1-5 .20 .30 .31 .32
6-20 .15 .26 .28 .28

Such that if I ordered 18 blue pens the value returned would be (.26)
It seems that I need a combination of both V & H Lookup.

Table Lookup using two data elements
 

If I change the formula to:

=INDEX(A1:E3,MATCH(2,INDEX(--LEFT(A1:A3),)),MATCH("blue",A1:E1,))

I still get the same value returned.



"Teethless mama" wrote:

Works on my machine

"M.O.R." wrote:

That will find a match on the color but will not return the correct value.
The value returned for "blue" is dependent upon how many are ordered. If 1-5
are ordered then the valule should be (.30) and if 6-20 are ordered then the
value should be (.26)

"Teethless mama" wrote:

Try this:

=INDEX(A1:E3,MATCH(18,INDEX(--LEFT(A1:A3),)),MATCH("blue",A1:E1,))


"M.O.R." wrote:

Hello,

I would like to be able to look up a price depending on both color & count
of items.
My table looks like:

# red blue green black
1-5 .20 .30 .31 .32
6-20 .15 .26 .28 .28

Such that if I ordered 18 blue pens the value returned would be (.26)
It seems that I need a combination of both V & H Lookup.

Table Lookup using two data elements
 

Assuming your table as posted within A1:E3,
and your limits in A2:A3 were changed to just the numbers: 1, 6

With inputs
In G1: 18
In H1: blue

Then in I1:
=IF(G120,"Out-of-range",INDEX($B$2:$E$3,MATCH(G1,$A$2:$A$3),MATCH(H 1,$B$1:$E$1,0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik
---

Table Lookup using two data elements
 

TM's formula does work but I would suggest you change your table. From this:

# red blue green black
1-5 .20 .30 .31 .32
6-20 .15 .26 .28 .28

To this:

...........A......B.....C......D.......E.........F
1....From....To...red...blue...green...black
2....1...........5.....20....30......31.......32
3....6.........20.....15....26......28.......28

Then:

H1 = lookup_value quantity = 18
H2 = lookup_value color = blue

=VLOOKUP(H1,A1:F3,MATCH(H2,A1:F1,0))

Note that any quantity in H1 that is 6 will return the price from that row
of the table.

--
Biff
Microsoft Excel MVP


"M.O.R." wrote in message
...
If I change the formula to:

=INDEX(A1:E3,MATCH(2,INDEX(--LEFT(A1:A3),)),MATCH("blue",A1:E1,))

I still get the same value returned.



"Teethless mama" wrote:

Works on my machine

"M.O.R." wrote:

That will find a match on the color but will not return the correct
value.
The value returned for "blue" is dependent upon how many are ordered.
If 1-5
are ordered then the valule should be (.30) and if 6-20 are ordered
then the
value should be (.26)

"Teethless mama" wrote:

Try this:

=INDEX(A1:E3,MATCH(18,INDEX(--LEFT(A1:A3),)),MATCH("blue",A1:E1,))


"M.O.R." wrote:

Hello,

I would like to be able to look up a price depending on both color
& count
of items.
My table looks like:

# red blue green black
1-5 .20 .30 .31 .32
6-20 .15 .26 .28 .28

Such that if I ordered 18 blue pens the value returned would be
(.26)
It seems that I need a combination of both V & H Lookup.