Multiple Vlookup’s

[Pank]

I have a sheet called lookup that has the following data columns:-

Col A (Print Size) has values like 7*5, 8*6, 8*8, etc.

Col B (No of Prints) has values starting at 100 and going up in steps of 25
up to and including 300.

Col C (Price Per Print) has values of how much it would cost to get each
print multiplied by the number of prints (i.e. x pence * 100 Prints).

Col D (Individual Price Print), has a value for a single print multiplied by
a whole number (i.e. x pence * 20).

A sample screen shoot would look like:-

Print Size No of Prints Price Per Print Individual Price Print
7*5 100 39.00 1.95
7*5 125 49.00 1.95
7*5 150 59.00 1.95
7*5 175 69.00 1.95

8*6 100 50.00 2.50
8*6 125 63.00 2.50
8*6 150 75.00 2.50
8*6 175 88.00 2.50

8*8 100 75.00 3.50
8*8 125 85.00 3.50
8*8 150 95.00 3.50
8*8 175 105.00 3.50
etc.
etc.
etc.

I have a sheet called Master where I will input the Print Size in a cell
(col C, e.g. 8*6) and number of prints in an adjoining cell (col D, 175). I
need a look up to populate col E with a value of 88 (from the example above).

Any assistance offered would be most welcome.

 

[Dave Peterson]

One way:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

 

[Toppers]

=INDEX($C$2:$C$15,MATCH(1,($A$2:$A$15="8*6")*($B$2:$B$15=175),0))

Enter as an array formula with Ctrl-Shift-Enter

HTH

 

[Pank]

Dave and Toppers,

Firstly many thanks for both your suggestions which worked a treat.

I opted for Dave solution as it saves me changing the formula each time.
Dave's solution offers flexibility.

Once again, this forum does the business.

Regards