lookup in ta tabel with ttwo variables

[Palle korsholm]

In a woorkbook I have two sheets

In one sheet I got a tabel With exchange rates one coulom per month and one
row per currency
January February
USD XXX XXX
Euro XXX XXX

In the other sheet I am standing i cell D4 where I like to get the correct
exhange rate. I got one cell (B4) with the months name an an other (C4) with
the name of the currency.

Do any have an idear ?

 

[Mike H]

Hi,

In this instance the table of exc rates and months is laid out like this
Col A Col B Col C Col D Col E Etc
jan feb mar apr etc
usd 1 2 1.5 1.2
euro 3 4 1.6 1.7
etc

and the formula in D4 is
=SUMPRODUCT((Sheet3!B1:M1=B4)*(Sheet3!A2:A23=C4)*(Sheet3!B2:M23))

Mike

 

[Palle korsholm]

I have lookd at it but I might not have been clear about what i ment.

If I wright February in cell B4 and USD in cell C4 i like the exchangerate
to gent in tho d4. If i change the names in B4 and C4 I will get the new
excahgnerate

 

[Mike H]

Which is exactly what the formula does.
In sheet 3 cell B1 to M1 are the months Jan- December
In column A starting in A2 are as many currencies as you want
At the intersect of these is the exchange rate

The formula

=SUMPRODUCT((Sheet3!B1:M1=B4)*(Sheet3!A2:A23=C4)*(Sheet3!B2:M23))

looks in B1 to M1 and find a match for B4
looks in A2 to A23 and find a match for C4
and returns the value of the cell at the intersect

The only caveat is that if your table extends to a second year then for date
you will have to include Jan 2008 and Jan 2009 etc.

Mike

 

[Palle korsholm]

Thank you for the help - this is realy usfull

 

[Sandy Mann]

Try:

=HLOOKUP(B4,Sheet2!A1:M12,MATCH(C4,Sheet2!A1:A12,FALSE),FALSE)

or with error checking:

=IF(COUNTA(B4:C4)=2,HLOOKUP(B4,Sheet2!A1:M12,MATCH(C4,Sheet2!A1:A12,FALSE),FALSE),"")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Mike,


Can you get your formula to work? The arrays in a SUMPRODUCT() must be of
the same dimensions otherwise some of the elements will not have anything to
multiply. I get #VALUE! from your formula.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mike H]

Sandy,

I get the value at the intersect of month/currency

Mike

 

[Sandy Mann]

So do I on a fresh workbook, I don't know what was wrong - my apologies.
However, I see that you are multiplying a Row by a Column - don't tell Bob
Philips - from his page on SUMPRODUCT():

*********************************
In a SUMPRODUCT function, the arrays being evaluated cannot be a mix of
column and row ranges, they must all be columns, or all rows.
*********************************

It seems to me that it works because there is no possibility that the can be
duplicate values in the first two arrays and the third array is a mixture of
Rows & Columns. ( ie either all columns or all rows in the third array
produces a #N/A error).

It obviously won't matter very much here but I wonder what the calculation
overhead would be with large arrays using this method.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Sandy,

I get the value at the intersect of month/currency

Mike

 

[Mike H]

Sandy,

Apologies aren't necessary. I've used this sucessfully many times and don't
believe it breaks any rules concerning sumproduct ranges, in my simplistic
view of things all rows are the same length as are all columns. I don't
understand the BP comment you note below because it does seem to suggest that
this shouldn't work but perhaps there are ways in which sumproduct will fall
over if used like this.

Incidentally it works perfectly well if you have duplicates in either/both
of the first 2 arrays. Stick 2 january's in and it sums both, hence my caveat
in my original response to the OP about changing the date to jan 2008 or 9.

Mike

 

[Ron Coderre]

The Rows x Columns issue highlights one of the
differences between the two SUMPRODUCT constructs:
=SUMPRODUCT(rng1,rng2)
vs
=SUMPRODUCT(rng1*rng2)

With
A1: (blank)
A2:A6 containing: 1; 2; 3; 4; 5
B11 containing: 10; 20; 30

This formula fails:
=SUMPRODUCT(A2:A6,B11)...returns #VALUE!

where this formula succeeds:
=SUMPRODUCT(A2:A6*B11)...returns 900

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)