Lookup with two variable data list cells

[Monkey]

I am attempting to create a simple quotation sheet

I have one cell in Sheet1 validated as list:
C1 - Customer Price-band Category (select from list)

and cells each refering to:
A1:A100 - Part Numbers (select from list)

In Sheet2, I have price lists for each Price-band Category against each Part
Number.

In the cell adjacent to the Part Number in Sheet1, I would like to display
the relevant part price depending on which Price-band Category and which Part
Number has been selected

I would appreciate any thoughts...

[Peo Sjoblom]

One possible way

=INDEX(Price_Range,MATCH(1,(Part_Number_Range=lookup1)*(Price_Band_Range=loo
kup2),0))

entered with ctrl + shift & enter


--

Regards,

Peo Sjoblom


"Monkey" <[email protected]> wrote in message
news:[email protected]...
> I am attempting to create a simple quotation sheet
>
> I have one cell in Sheet1 validated as list:
> C1 - Customer Price-band Category (select from list)
>
> and cells each refering to:
> A1:A100 - Part Numbers (select from list)
>
> In Sheet2, I have price lists for each Price-band Category against each
Part
> Number.
>
> In the cell adjacent to the Part Number in Sheet1, I would like to display
> the relevant part price depending on which Price-band Category and which
Part
> Number has been selected
>
> I would appreciate any thoughts...

[Monkey]

Thanks Peo

I tried that but, alas, to no avail.

Example I tried is:

H I J
21 List (Y,Z)) List (A,B)
22
23 A B
24 Y Pears Apples
25 Z Lemons Bananas

So, to reference a value within I24:J25, dependant on selected entries of
I21 and J21, I tried:

=INDEX(I23:J25,MATCH(1,(H24:H25=I21)*(I23:J23=J21),0)) entered with ctrl
+shift & enter

This did not work

Any ideas where I went wrong?

Much obliged, Monkey

"Peo Sjoblom" wrote:

> One possible way
>
> =INDEX(Price_Range,MATCH(1,(Part_Number_Range=lookup1)*(Price_Band_Range=loo
> kup2),0))
>
> entered with ctrl + shift & enter
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "Monkey" <[email protected]> wrote in message
> news:[email protected]...
> > I am attempting to create a simple quotation sheet
> >
> > I have one cell in Sheet1 validated as list:
> > C1 - Customer Price-band Category (select from list)
> >
> > and cells each refering to:
> > A1:A100 - Part Numbers (select from list)
> >
> > In Sheet2, I have price lists for each Price-band Category against each
> Part
> > Number.
> >
> > In the cell adjacent to the Part Number in Sheet1, I would like to display
> > the relevant part price depending on which Price-band Category and which
> Part
> > Number has been selected
> >
> > I would appreciate any thoughts...
>
>
>