Help with a lookup problem

[PM1]

Hi All, I have a simple Spreadsheet that racks what we buy and converts from multiple currencies to a USD & EUR figure

1.00 QAR 38400.000 38400.000 €7,680.00 $10,971.43

So Qty 1 x QAR38400 = €7680 or $10971.43 depending upon Exchange Rates

My rates are in a table and fixed each month so at present I use the formula +total val * vlookup(Currecy, table, col no,0)* qty for Eur and Division for USD.

2013 2014
Month JAN JAN JAN JAN
Currency € $ € $
AED 0.2 3.5 0.2 3.500
BHD 2 0.4 2 0.350
USD 0.7 1.0 0.7 1.000
QAR 0.2 3.5 0.2 3.500
KWD 2.6 0.3 2.6 0.269
SAR 0.2 3.5 0.2 3.500
EUR 1 0.7 1 0.700
GBP 1.2 0.6 1.2 0.583
CHF 0.8 0.875 0.8 0.875
MAD 0.1 7.0 0.1 7.000
ZAR 0.1 7.0 0.1 7.000

Now in my main table I have a date field and what I need to do is for the lookup to look at the date field and use the Month to lookup in the exchange table and bring that result, bearing in mind that each month I will add new column to represent that month. Only 2013 is fixed.

 

[thisoldman]

A little more information about the lookup table is needed. Is it set up like this?

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD][/TD]
[TD]2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]JAN[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]FEB[/TD]
[TD]...[/TD]
[TD]JAN[/TD]
[TD]JAN[/TD]
[/TR]
[TR]
[TD]Currency[/TD]
[TD]€[/TD]
[TD]$[/TD]
[TD]€[/TD]
[TD]$[/TD]
[TD]...[/TD]
[TD]€[/TD]
[TD]$[/TD]
[/TR]
[TR]
[TD]AED[/TD]
[TD]0.2 [/TD]
[TD]3.5[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]0.2[/TD]
[TD]3.5[/TD]
[/TR]
</tbody>[/TABLE]

Are the months in row 2 text or are they formatted dates? Formatted dates might allow lookup formulas with a few less nested functions.

 

[PM1]

Yes, except I use 1,2 to represent Jan,Feb

I was trying IF($C1969='SAP Exc Rates'!$B$1,SUM($I1969)*VLOOKUP($H1969,'SAP Exc Rates'!$A$4:$AA$14,4,0)*$G1969,IF($C1969='SAP Exc Rates'!D1,AND('Master LPO Database'!B1969='SAP Exc Rates'!D2),VLOOKUP($H1969,'SAP Exc Rates'!$A$4:$AA$14,4,0)*$G1969)) but slowly realised that it was going to become a never ending formula.

Just to advise that I would use Jennies html function but my works firewall blocks that, sorry

 

[thisoldman]

Here is what I have to determine the conversion factors. I just wanted to show what the lookup portion of the larger formula might be.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

BHD

Month

AED

Currency

AED

BHD

USD

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #ccccff"]Date[/TD]
[TD="bgcolor: #ccccff"]Local
Currency[/TD]
[TD="bgcolor: #ccccff"]Euro[/TD]
[TD="bgcolor: #ccccff"]USD[/TD]

[TD="bgcolor: #ffffcc, align: right"]2013[/TD]
[TD="bgcolor: #ffffcc"][/TD]
[TD="bgcolor: #ffffcc"][/TD]
[TD="bgcolor: #ffffcc"][/TD]
[TD="bgcolor: #ffffcc"][/TD]
[TD="bgcolor: #ffffcc"][/TD]
[TD="bgcolor: #ccccff, align: right"]2014[/TD]
[TD="bgcolor: #ccccff"][/TD]
[TD="bgcolor: #ccccff"][/TD]
[TD="bgcolor: #ccccff"][/TD]
[TD="bgcolor: #ccccff"][/TD]
[TD="bgcolor: #ccccff"][/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]Feb 2, 2013[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #ffffcc, align: right"]1[/TD]
[TD="bgcolor: #ffffcc, align: right"]1[/TD]
[TD="bgcolor: #ffffcc, align: right"]2[/TD]
[TD="bgcolor: #ffffcc, align: right"]2[/TD]
[TD="bgcolor: #ffffcc, align: right"]3[/TD]
[TD="bgcolor: #ffffcc, align: right"]3[/TD]
[TD="bgcolor: #ccccff, align: right"]1[/TD]
[TD="bgcolor: #ccccff, align: right"]1[/TD]
[TD="bgcolor: #ccccff, align: right"]2[/TD]
[TD="bgcolor: #ccccff, align: right"]2[/TD]
[TD="bgcolor: #ccccff, align: right"]3[/TD]
[TD="bgcolor: #ccccff, align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]Feb 2, 2014[/TD]

[TD="align: right"]99[/TD]
[TD="align: right"]101[/TD]

[TD="bgcolor: #ffffcc"]€[/TD]
[TD="bgcolor: #ffffcc"]$[/TD]
[TD="bgcolor: #ffffcc"]€[/TD]
[TD="bgcolor: #ffffcc"]$[/TD]
[TD="bgcolor: #ffffcc"]€[/TD]
[TD="bgcolor: #ffffcc"]$[/TD]
[TD="bgcolor: #ccccff"]€[/TD]
[TD="bgcolor: #ccccff"]$[/TD]
[TD="bgcolor: #ccccff"]€[/TD]
[TD="bgcolor: #ccccff"]$[/TD]
[TD="bgcolor: #ccccff"]€[/TD]
[TD="bgcolor: #ccccff"]$[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #ffffcc, align: right"]11[/TD]
[TD="bgcolor: #ffffcc, align: right"]22[/TD]
[TD="bgcolor: #ffffcc, align: right"]33[/TD]
[TD="bgcolor: #ffffcc, align: right"]44[/TD]
[TD="bgcolor: #ffffcc, align: right"]55[/TD]
[TD="bgcolor: #ffffcc, align: right"]66[/TD]
[TD="bgcolor: #ccccff, align: right"]77[/TD]
[TD="bgcolor: #ccccff, align: right"]88[/TD]
[TD="bgcolor: #ccccff, align: right"]99[/TD]
[TD="bgcolor: #ccccff, align: right"]101[/TD]
[TD="bgcolor: #ccccff, align: right"][/TD]
[TD="bgcolor: #ccccff, align: right"][/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #ffffcc, align: right"]8[/TD]
[TD="bgcolor: #ffffcc, align: right"]7[/TD]
[TD="bgcolor: #ffffcc, align: right"]6[/TD]
[TD="bgcolor: #ffffcc, align: right"]5[/TD]
[TD="bgcolor: #ffffcc, align: right"]4[/TD]
[TD="bgcolor: #ffffcc, align: right"]3[/TD]
[TD="bgcolor: #ccccff, align: right"]2[/TD]
[TD="bgcolor: #ccccff, align: right"]1[/TD]
[TD="bgcolor: #ccccff, align: right"]0.9[/TD]
[TD="bgcolor: #ccccff, align: right"]0.8[/TD]
[TD="bgcolor: #ccccff, align: right"][/TD]
[TD="bgcolor: #ccccff, align: right"][/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #ffffcc, align: right"]2.5[/TD]
[TD="bgcolor: #ffffcc, align: right"]1[/TD]
[TD="bgcolor: #ffffcc, align: right"]2.4[/TD]
[TD="bgcolor: #ffffcc, align: right"]1[/TD]
[TD="bgcolor: #ffffcc, align: right"]2.3[/TD]
[TD="bgcolor: #ffffcc, align: right"]1[/TD]
[TD="bgcolor: #ccccff, align: right"]2.2[/TD]
[TD="bgcolor: #ccccff, align: right"]1[/TD]
[TD="bgcolor: #ccccff, align: right"]2.1[/TD]
[TD="bgcolor: #ccccff, align: right"]1[/TD]
[TD="bgcolor: #ccccff, align: right"][/TD]
[TD="bgcolor: #ccccff, align: right"][/TD]

</tbody>

Cell Formulas, copied downward C2 =INDEX(IF(YEAR(A2)=2013, $G$4:$L$6, $M$4:$R$6), MATCH(B2, $F$4:$F$6, 0), MATCH(MONTH(A2), $G$2:$L$2, 0)) D2 =INDEX(IF(YEAR(A2)=2013, $G$4:$L$6, $M$4:$R$6), MATCH(B2, $F$4:$F$6, 0), MATCH(MONTH(A2), $G$2:$L$2, 0) + 1) <tbody> </tbody>

<tbody>
</tbody>

I have divided what I had hoped would be one lookup table into two lookup tables. The currency conversion rates for 2013 are in columns G through L, 2014 are M through R. If your conversion table had dates, Excel month and year in single cells, I could have done away with the IF.

The first MATCH determines which row in the table to use. The second MATCH determines the column. The USD is one column to the right of the Euro, so in D2, the column to match is the Euro column plus one.

To extend this into later years, 2015 and beyond, will require nested IFs.

Perhaps someone else has a better solution for the lookup.