Lookup - but with two criteria

[BigDelGooner]

In deperate need of some help here..

I have a worksheet with data set out as follows (the slimmed down version and rates just jotted in):

RefCcy SecCcy FX Rate
GBP GBP 1.0
EUR EUR 1.0
GBP EUR 0.8
EUR GBP 1.4
EUR USD 1.2
USD USD 1.0
USD GBP 1.6

My problem here is that, for example, I need a formula that is going to lookup the FX rate when the RefCcy is EUR and the SecCcy is GBP or when the RefCcy is USD and SecCcy is GBP. I'd like to avoid the VBA code if possible.

Any ideas guys? Thank you in advance for your help.

 

[jimrward]

you could use an additional help column that joins up the REC and SEC codes, and use this to do the LOOKUP or MATCH

 

[EB08]

^^^that works; but do a quick search of the forum for multi conditional lookups or something of the sort...there should be a lot out there.

In the case that may not want to have a 'helper column'; something like the following should work for you.

INDEX(FxRate,MATCH(1,(RefCcy=E2)*(SecCcy=F2),0))

confirmed with Ctrl+Shift+Enter

RefCcy is the range holding the 'RefCcy' values; same with SecCcy and FxRate ranges.

E2 holds the desired RefCcy value, F2 holds the desired SecCcy value.

 

[Dan Waterloo]

There's also the SUMPRODUCT version:

=SUMPRODUCT((E2=RefCcy)*(F2=SecCcy)*FxRate)

This uses the same range conventions that EB08 stated. SUMPRODUCT can be used as a regular function (without Ctl-Shift-Enter).

 

[bosco_yip]

=LOOKUP(2,1/((E2=RefCcy)*(F2=SecCcy)),FxRate)

Or…

=LOOKUP(2,1/((E2=A2:A100)*(F2=B2:B100)),C2:C100)

Regards

 

[BigDelGooner]

Thank you very much everyone, problem resolved.

Mrexcel delivers again