Table Lookup using two data elements

[M.O.R.]

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.

 

[M Kan]

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.

 

[Teethless mama]

Try this:

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

 

[M.O.R.]

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]

Works on my machine

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)

 

[M.O.R.]

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.

 

[Max]

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(G1>20,"Out-of-range",INDEX($B$2:$E$3,MATCH(G1,$A$2:$A$3),MATCH(H1,$B$1:$E$1,0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik
---

 

[T. Valko]

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.