Table Lookup using two data elements

M

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

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.
 
T

Teethless mama

Try this:

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

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)
 
T

Teethless mama

Works on my machine

M.O.R. said:
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

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.
 
M

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top