Multiple Vlookups in a range

[John]

Hi,

I am trying to return a price based on multiple Vlookups and the number of
items ordered.

I have seen the examples of multiple columns lookups, but I can not figure
out how to add then "range" component.

Here is the table:

Category Product Start End Price
Hardware Prod A 1 2 $ 20.00
Hardware Prod A 3 5 $ 18.00
Hardware Prod A 6 10 $ 16.00
Hardware Prod B 1 2 $ 15.00
Hardware Prod B 3 5 $ 13.00
Hardware Prod B 6 10 $ 11.00
Hardware Prod C 1 2 $ 10.00
Hardware Prod C 3 5 $ 9.00
Hardware Prod C 6 10 $ 7.00
Software Prod A 1 2 $ 30.00
Software Prod A 3 5 $ 28.00
Software Prod A 6 10 $ 26.00
Software Prod B 1 2 $ 25.00
Software Prod B 3 5 $ 24.00
Software Prod B 6 10 $ 23.00


I know how to get the multiple match

=INDEX($E$2:$E$16,MATCH("Hardware",IF($B$2:$B$16="Prod B",$A$2:$A$16),0))

which returns the value of $15.00.

But how can I do the lookup of the range in columns C and D when the person
orders 4 items? I want to return $13.00.

Thanks,
John

 

[Aladin Akyurek]

Your current lookup table is set in ascending order on column A, B, and C in
that order. Assuming this, do:

Step 1. Insert a column before the current column A.
Step 2. Enter the label Concat in A1.
Step 3. Enter the following formula in A2 and copy down:

=B2&","&C2

A2: F16 should now house the lookup table of interest.

Let H2 house the lookup value of Hardware, I2 the lookup value of Prod B,
and J2 the lookup value of 4, the quantity.

In K2 enter:

=LOOKUP(J2,INDEX($D$2:$D$16,MATCH(H2&","&I2,$A$2:$A$16,0)):INDEX($F$2:$F$16,
MATCH(H2&","&I2,$A$2:$A$16,1)))

which is an ordinary, with just enter confirmed, formula.

 

[John]

Aladin,

Thanks. I just tried you approach and it works.

It does assume that I create an extra column (Concat) and ensure that the
column is sorted. Any way around this?

Thanks,
John

 

[Aladin Akyurek]

John,

Sorting is already required for the Start/End data. Sorting the data area on
Category and Product gives you to use an efficient method to retrieve a
price of interest.

Aladin