|
lookup in ta tabel with ttwo variables
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 ? -- Palle korsholm |
lookup in ta tabel with ttwo variables
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" wrote: 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 ? -- Palle korsholm |
lookup in ta tabel with ttwo variables
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 -- Palle korsholm "Mike H" wrote: 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" wrote: 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 ? -- Palle korsholm |
lookup in ta tabel with ttwo variables
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" wrote: 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 -- Palle korsholm "Mike H" wrote: 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" wrote: 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 ? -- Palle korsholm |
lookup in ta tabel with ttwo variables
Thank you for the help - this is realy usfull
-- Palle korsholm "Mike H" wrote: 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" wrote: 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 -- Palle korsholm "Mike H" wrote: 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" wrote: 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 ? -- Palle korsholm |
lookup in ta tabel with ttwo variables
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 Replace @mailinator.com with @tiscali.co.uk "Mike H" wrote in message ... 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" wrote: 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 -- Palle korsholm "Mike H" wrote: 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" wrote: 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 ? -- Palle korsholm |
lookup in ta tabel with ttwo variables
Sandy,
I get the value at the intersect of month/currency Mike "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "Mike H" wrote in message ... 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" wrote: 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 -- Palle korsholm "Mike H" wrote: 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" wrote: 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 ? -- Palle korsholm |
lookup in ta tabel with ttwo variables
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 Replace @mailinator.com with @tiscali.co.uk "Mike H" wrote in message ... Sandy, I get the value at the intersect of month/currency Mike "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "Mike H" wrote in message ... 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" wrote: 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 -- Palle korsholm "Mike H" wrote: 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" wrote: 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 ? -- Palle korsholm |
lookup in ta tabel with ttwo variables
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 "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "Mike H" wrote in message ... Sandy, I get the value at the intersect of month/currency Mike "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "Mike H" wrote in message ... 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" wrote: 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 -- Palle korsholm "Mike H" wrote: 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" wrote: 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 ? -- Palle korsholm |
lookup in ta tabel with ttwo variables
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 B1:D1 containing: 10; 20; 30 This formula fails: =SUMPRODUCT(A2:A6,B1:D1)...returns #VALUE! where this formula succeeds: =SUMPRODUCT(A2:A6*B1:D1)...returns 900 -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Sandy Mann" wrote in message ... 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 Replace @mailinator.com with @tiscali.co.uk "Mike H" wrote in message ... Sandy, I get the value at the intersect of month/currency Mike "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "Mike H" wrote in message ... 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" wrote: 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 -- Palle korsholm "Mike H" wrote: 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" wrote: 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 ? -- Palle korsholm |
| All times are GMT +1. The time now is 04:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com