Index and Match

[Alia]

PLEASE PLEASE PLEASE HELP!!

I want to create a formula to help with pricing.
I have a list of data in columns: Model Number, Processor Number, Edition
Number and CPW
I have created a data validation list for the Model No, Processor No and
Edition No so that these can be picked and need the CPW to automatically be
input into the cell below for the prcing to be calculated. This is the
formula I have done
=INDEX((K5:M19,K24:M28),MATCH(R8,Model.,0),MATCH(R10,Proc,0),MATCH(R12,Edition,0)) but it doesn't work!!!

PLEASE HELP!

 

[Ashish Mathur]

Hi,

Try this

=index(D1:E50,match(A51&B51&C51,E1:E50,0),1)

In cell E2, type A2&B2&C2 and copy this down till E50. A51, B51 and C51
hold your values from the drop down selection. Col D holds the CPW.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Alia]

I can't get it to work: assume the following:-

Extract 1
Model Number 270
Processor Edition CPW
2248 1517 150
2250 1516 370
2250 1518 370

Extract 2
Model Number 515
Processor Edition CPW
8327 6010 3800
8327 6011 3800
8327 6018 3800

The data validation list is

Model Number 270

Processor 2250

Edition 1516

cpw THIS I CAN'T GET TO WORK??

 

[T. Valko]

If the processor number and edition number are unique to a model number then
you can use something like this:

=SUMPRODUCT(--(A1:A15=2250),--(B1:B15=1516),C1:C15)

Where:

Column A = processor number
Column B = edition number
Column C = CPW