lOOKUP IN A MATRIX ARRAY

[mart10]

I am using Excel 2003

I have a rates sheet on workbook as below (rates are wrong in example shown, but you get the point)
AUD CAD GBP
AUD 1.0 1.2 0.75
CAD 0.8 1.0 0.9
GBP 1.5 1.6 1.0

etc

In another tab on workbook I have a foreign exchange deal with multiple possiblities of currency pairs (same currencies as in rates sheet)

The data looks as following example:

BuyCcy SellCcy Rate BuyAmount Sell Amount
GBP CAD ? from the array
CAD GBP ? from the array


What I need to do is some sort of Vlookup and Hlookup based upon the currency pair to extract the rate in the array above. I have looked at index array and lookups but just cant seem to get the syntax correct. Also as you can see depending on which currency I pick first will affect the rate exracted eg GBPCAD will give 1.6 CADGBP will give 0.9 (Vertical then Horiz)- I will probably want to do this

I need urgent help

 

[SkipVought]

Hi,

Use Index and Match

If this is what you expect...
[tt]
F G H
BuyCcy SellCcy Rate
GBP CAD 1.6
CAD GBP 0.9
[/tt]
then the formula is..
[tt]
H2: =INDEX($B$2:$D$4,MATCH($F2,$A:$A,0)-1,MATCH($G2,$1:$1,0)-1)
[/tt]
where $B$2:$D$4 is the DATA RANGE of your conversion table.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mart10]

its eaxctly what i wanted but DOESNT GIVE QUITE THE RESULTS
I have had to modify as my data was in different sheet - Matrix!G$1:$BI$57

=INDEX(Matrix!G$1:$BI$57,MATCH($K2,Matrix!$F:$F,0),MATCH($M2,Matrix!$1:$1,0)-1)

It seems to lookup the first part ok ie column F where the ccy is (I took off the -1) but then doesnt do the H Lookup on the next ccy. It needs to find the intersection of the 2 ccys

 

[SkipVought]

your lookup table does not anchor in Matrix!A1 -- rather Matrix!F1.

Therefore, you must account for the difference in the horizontal offset...
[tt]
=INDEX(Matrix!G$1:$BI$57,MATCH($K2,Matrix!$F:$F,0),MATCH($M2,Matrix!$1:$1,0)-6)

[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mart10]

eR NOT QUITE IT WORKS WITH -5! Anyway this is absolutley great

Thanyou so much, Can you maybe sometime explain how this works?

 

[SkipVought]

Look in HELP for INDEX and MATCH.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}