If/Then and look up in same formula...must be an easier way

[franathive]

Hi,
I have the following situation. I am trying to look up months and currency conversion rates. I have a table which I am maintaining that looks like this.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1/1/2018[/TD]
[TD]2/1/2018[/TD]
[TD]3/1/2018[/TD]
[TD]4/1/2018[/TD]
[TD]5/1/2018[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]GBP[/TD]
[TD]1.2[/TD]
[TD]1.3[/TD]
[TD]1.4[/TD]
[TD]1.5[/TD]
[TD]1.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EUR[/TD]
[TD]1.2[/TD]
[TD]1.25[/TD]
[TD]1.26[/TD]
[TD]1.27[/TD]
[TD]1.28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]USD[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

On a different tab, I have a list of invoices.
It looks like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer NAME
Customer A[/TD]
[TD]INVOICE DATE
1/15/2018[/TD]
[TD]Current drop down
GBP[/TD]
[TD]I want this column to be a formula driven
1.2[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]3/15/2018[/TD]
[TD]EUR[/TD]
[TD]1.26[/TD]
[/TR]
[TR]
[TD]Customer C[/TD]
[TD]2/5/2018[/TD]
[TD]USD[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Customer D[/TD]
[TD]1/17/2018[/TD]
[TD]GBP[/TD]
[TD]1.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

There must be an easier way than if/then and < and > symbols. These formulas are getting long.
Basically, I have lookup currency on chart, if invoice date is greater than x and less than y, then z. But it is getting cumbersome. Am I missing something easy?

 

[jtakw]

Hi,

How about this:

Change/adjust formula with your Tab name where your Table is.

Book1
	A	B	C	D	E	F
1		1/1/2018	2/1/2018	3/1/2018	4/1/2018	5/1/2018
2	GBP	1.2	1.3	1.4	1.5	1.6
3	EUR	1.2	1.25	1.26	1.27	1.28
4	USD	1	1	1	1	1
5
6
7	Customer NAME	INVOICE DATE	Current drop down
8	Customer A	1/15/2018	GBP	1.2
9	Customer B	3/15/2018	EUR	1.26
10	Customer C	2/5/2018	USD	1
11	Customer D	1/17/2018	GBP	1.2
Sheet6

Cell Formulas
Range	Formula
D8	=SUMPRODUCT((B$1:F$1=LOOKUP(B8,B$1:F$1))*(A$2:A$4=C8)*(B$2:F$4))

Formula copied down.

 

[steve the fish]

Another way using INDEX MATCH MATCH

=INDEX($1:$4,MATCH(C8,$A$1:$A$4,0),MATCH(B8,$1:$1))

 

[franathive]

Thank you so much! This worked like a charm. I do feel a bit like it's a black hole. What confuses me about your formula-how does Excel know that 1/15/2018 goes to 1/1/2018 as it is not a one to one match?

I can't figure out how your formula works and replaces my ridiculously long one! I will use it but any insight is appreciated. Thank you. As for the other poster who posted the Index and Match, I have never used those commands but did think that was the way to go. I will use the first formula simply because I think I'll be able to understand how it works with a bit of research. I hate to use a formula I don't understand.

You guys are amazing. Thank you!

Hi,

How about this:

Change/adjust formula with your Tab name where your Table is.

	A	B	C	D	E	F
GBP
EUR
USD
Customer NAME	INVOICE DATE	Current drop down
Customer A	GBP
Customer B	EUR
Customer C	USD
Customer D	GBP

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]3/1/2018[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]5/1/2018[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1.2[/TD]
[TD="align: right"]1.3[/TD]
[TD="align: right"]1.4[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]1.6[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1.2[/TD]
[TD="align: right"]1.25[/TD]
[TD="align: right"]1.26[/TD]
[TD="align: right"]1.27[/TD]
[TD="align: right"]1.28[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

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

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

[TD="align: center"]7[/TD]

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

[TD="align: center"]8[/TD]

[TD="align: right"]1/15/2018[/TD]

[TD="align: right"]1.2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3/15/2018[/TD]

[TD="align: right"]1.26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]2/5/2018[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1/17/2018[/TD]

[TD="align: right"]1.2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D8[/TH]
[TD="align: left"]=SUMPRODUCT((B$1:F$1=LOOKUP(B8,B$1:F$1))*(A$2:A$4=C8)*(B$2:F$4))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formula copied down.

 

[jtakw]

Thank you so much! This worked like a charm.-how does Excel know that 1/15/2018 goes to 1/1/2018 as it is not a one to one match?

I think I'll be able to understand how it works with a bit of research. I hate to use a formula I don't understand.

You guys are amazing. Thank you!

You're welcome, welcome to the forum.

The LOOKUP function within my formula looks for B8 (1/15/2018) in the range B1:F1, as long as the range is in ascending order, and since LOOKUP cannot find an exact match for 1/15/2018, it returns the next Smaller value, in this case 1/1/2018.

 

[franathive]

That makes perfect sense. Thank you...this is why I use the word FALSE in formulas...because I usually want an exact match. Funny, I always thought it was odd and wondered why someone would need something that wasn't an exact match. This is the perfect example!! Thank you for all your help. Very much appreciated.

You're welcome, welcome to the forum.

The LOOKUP function within my formula looks for B8 (1/15/2018) in the range B1:F1, as long as the range is in ascending order, and since LOOKUP cannot find an exact match for 1/15/2018, it returns the next Smaller value, in this case 1/1/2018.