Hi Forum,

can someone give me a nudge in the right direction with this?
I have a table linked from Access:
The table can grow as new articles/suppliers are added, I would like to take this into account in the soloution.
(Note. Some articles have more than 1 supplier)

A B .................. P
Supplier ArticleNo Price
80001 1235 10,95
80002 1000 18,99
80002 1876 15,32
80001 1000 13,42
80002 1235 11,30

How can I construct a formula to extract the price for a given article from a particular supplier?
I don't really want to resort to VBA with this.

Thanks for your consideration

Nick Ellis

Howdy. First with regard to the data, use a dynamic named range, called DataRng:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

Now for the formula, you can use at least two approaches. This is the way I typically approach it. It adds an extra column, but not as intense later on. One is to add another column (perhaps to the left of the data, so that data can grow to right). Thus, your supplier is in Column B and Article in Column C. Then label that new column MyLU (in A1), and in A2 put this formula:

=B2&C2

copy down as far as data.

Define this as a named range (MyLU):

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

And define name range Head:

=OFFSET(Sheet1!$A$1,0,0,1,COUNTA(Sheet1!$1:$1))

Then in a cell outside you range (X2 and X3), put the Suppliers for drop down in X2 and Article in X3, then in X4 put this formula:

=INDEX(DataRng,MATCH(X2&X3,MyLU,FALSE),MATCH("Pric e",Head,FALSE))

Now I have read myself clever, thanks for your effort !!!