Extracting data from a table

[dwayne]

My suppliers use different volume ranges for price breaks.

I have a cell that defines the supplier I want to use and
another one to define the volume I require. I would like
the "volume range" cell to be a formula/function that
displays the volume range based on the supplier and the
volume requred. See example below.


supplier A
volume required 56
volume range 0-59


Supplier Range1 Range2 Range3 Range4 Range5
A 0-59 60-100 101-200 201-500 501+
B 0-75 76-125 126-250 251-450 451+
C 0-50 51-100 101-250 251-500 501+

Thnaks for any advice.

 

[macropod]

Hi Dwayne,

Suppose your lookup table is in A1:F4, but is expressed as:
Supplier Range1 Range2 Range3 Range4 Range5
A 0 60 101 201 501
B 0 76 126 251 451
C 0 51 101 251 501
(ie only the lowest values for each range)

If the:
.. heading 'supplier name' is in A10
.. supplier name is in B10;
.. heading 'volume required' is in A11 and
.. volume required is in B11,
then the formula to return the name of the 'volume range' in A12 would be:
=OFFSET(A1,,MATCH(B11,OFFSET(A1:F1,MATCH(B10,A1:A4,0)-1,),1)-1)
and the formula to return the corresponding value in B12 would be:
=OFFSET(INDEX(B1:B4,MATCH(B10,A1:A4,0)),,MATCH(A12,B1:F1,0)-1)

Cheers
PS: If you wanted to have 'volume range' as the heading in A12, B12 could be
expressed as:
=OFFSET(INDEX(B1:B4,MATCH(B10,A1:A4,0)),,MATCH(OFFSET(A1,,MATCH(B11,OFFSET(A
1:F1,MATCH(B10,A1:A4,0)-1,),1)-1),B1:F1,0)-1)

 

[Domenic]

Hi Dwayne,

In order for the following to work, you'll need to change the "volume
range" in the column containing Range5 from "501+" to "500-over", "451+"
to "451-over", etc.

Using your table....

1) Select/highlight five cells, let's say H2 to L2
2) Press =
3) Enter the following formula:
=OFFSET($B$1:$F$1,MATCH($I$5,$A$2:$A$4,0),0)
entered using Ctrl+Shift+Enter

I5: enter supplier of interest

I6: enter volume required

I7:

=INDEX($H$2:$L$2,MATCH($I$6,--LEFT($H$2:$L$2,FIND("-",$H$2:$L$2)-1)))
entered using Ctrl+Shift+Enter

Hope this helps!