Finance problem - retrieving data based on two inputs

[claytorm]

Hi,

I have worked through about half of this problem, but am now stuck, an
would be very grateful for any help. This involves retrieving data fro
a long list of shares and prices based on inputs of company code an
date.

References refer to attached sheet.The unusual date formatting i
because I am from England.

I have data in three columns. In column A a company code (eg. uu), in
a date (eg. 23/06/2004), and in C a price (eg. 23)

In F1 input of company code, in F2 input of date. When BOTH thes
inputs match in the SAME row in columns A and B respectively, I want t
return that row number and convert it into an address for that row i
column C.

I have left a clean sheet for any suggestions.

Many thanks in advance for any solutions.

Berti

Attachment filename: testsheet.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=62070

 

[Frank Kabel]

Hi
try the following array formula (entered with cTRL+SHIFT+ENTER):
=MATCH(1,(A1:A100=F1)*(B1:B100=F2),0)
to return the row number.

If you want the associated price use
=INDEX(C1:C100,MATCH(1,(A1:A100=F1)*(B1:B100=F2),0))

and if you want to include error checking use
=IF(ISNA(MATCH(1,(A1:A100=F1)*(B1:B100=F2),0)),"no match
found",INDEX(C1:C100,MATCH(1,(A1:A100=F1)*(B1:B100=F2),0)))

all formulas are array formulas!

 

[claytorm]

Fantastic job! Works a treat. Thanks very much. Bertie