look up using 2 cells from one sheet to find a value in another...

[robbed]

Hi

Please could someone help me - really stuck !

basically what i want is to tell excel if a certain cell in my first work
sheet is xxxx and xxxx (data from two cells both contained in each work
sheet. ie date and price)

(so if cell A has date of 01/08/2007 and a code of eurgbp)

then excel should go to my second work sheet and find the relevant value for
the cell which also has a code of eurgbp on date 01/08/2007.

does that make sense ?

thanks, rob

 

[Carim]

Hi,

As usual, sumproduct() will do the job ...

=SUMPRODUCT(--(Sheet2!A2:A6=Sheet1!A2),--(Sheet2!B2:B6=Sheet1!
B2),Sheet2!C2:C6)

HTH

 

[robbed]

Hi

That is not what I asked....please could you re-read my question.....

I do not want the sum of anything - I need to locate different values across
a different worksheet according to values I have on my first worksheet.

thanks

 

[Carim]

Hi,

First Try the sumproduct() formula ...
and then see if you get your result ...

 

[robbed]

It does not work - it's not even applicable to what i am trying to acheive.....

 

[Dave Peterson]

If you're retrieving a numeric value from that second sheet and there's only one
row that matches both criteria, you may want to try that =sumproduct() formula
again.

If you're retrieving a text value
or
had multiple rows that matched the criteria, but only wanted the first row's
information brought back

Then =sumproduct() wouldn't work.

From your posts, it sounds like =sumproduct() would work ok--well, it sounds
like it would work ok to me.

 

[Carim]

From your explanation:
1. column A has Dates
2. column B has Currency Cross Rates
3. column C has Prices

so, as Dave said it, there is no reason for sumproduct() not to
work ...

Good Luck ...

 

[Pete_UK]

Can you describe the layout of data on your second sheet - columns
used, data to be retrieved, rows used etc.

Pete