Referencing a value from a table

[ddub25]

__________DK____________DL______DM______DN
_______Date_________Date_______ $_______€
_______From_________To
19_____01/01/2011___31/01/2011__0.6671__0.8470
20_____01/02/2011___28/02/2011__0.6671__0.8470
21_____01/03/2011___31/03/2011__0.6148__0.8581
22_____01/04/2011___30/04/2011__0.6000__0.8500

I want to reference a value from the table according to certain conditions. (DK, DL, DM, DN are columns - sorry about the crude table)

For example, cell AE367 contains the date 21/03/2011 and cell D367 contains the "€" currency symbol. In this case I want the formula to be able to look at the table and see that the 21/03/2011 falls between the two dates on row 21 and the € column contains 0.8581 for this row. This is the value I want referenced.

I have tried this with VLOOKUP and HLOOKUP with no luck. Can anyone help. Thanks.

Dan

 

[texasalynn]

Excel Workbook
	A	B	C	D
16	DK	DL	DM	DN
17	Date	Date	$
18	From	To
19	1/1/2011	1/31/2011	0.6671	0.847
20	2/1/2011	2/28/2011	0.6671	0.847
21	3/1/2011	3/31/2011	0.6148	0.8581
22	4/1/2011	4/30/2011	0.6	0.85
Sheet2

Excel 2003

Excel Workbook
	D
367
368	0.8581
Sheet2

Excel 2003

Cell Formulas
Range	Formula
D368	=SUMPRODUCT(--(A19:A22<=AE367),--(B19:B22>=AE367),(D19:D22))

 

[ddub25]

Thanks. The formula also needs to ask IF E367 contains a "€" sign or a "$". If it contains a "$" sign then take the value from column C (C21 = 0.6148). If it contains a "€" sign then take the value from column D (D21 = 0.8581).

How could this be incorporated?

 

[texasalynn]

does this work

Excel Workbook
	D	E
367	$
368	0.6148	0.8581
Sheet1

Excel 2003

Cell Formulas
Range	Formula
D368	=INDEX($DM$17:$DN$22,MATCH($AD$367,$DK$17:$DK$22),MATCH(D$367,$DM$17:$DN$17,0))
E368	=INDEX($DM$17:$DN$22,MATCH($AD$367,$DK$17:$DK$22),MATCH(E$367,$DM$17:$DN$17,0))

 

[ddub25]

Here is your formula:
=INDEX($DM$17:$DN$22,MATCH($AD$367,$DK$17:$DK$22),MATCH(D$367,$DM$17:$DN$17,0))

It returns #N/A (maybe because in the last bracket you have $DM$17:$DN$17, when maybe it should be: $DM$17:$DN$22

I adapted it to:
=INDEX($DM$17:$DN$22,MATCH(AE367,$DK$17:$DK$22),MATCH(D367,$DM$17:$DN$22,0))

changing the last bracket to $DM$17:$DN$22, and instead of $AD$367, I now have AE367 (column AE is the one i want referenced).

However, the formula still returns #N/A.

This formula does not consider column DL (Date To) and would not be able to know if the date in column AE is between the dates in columns DK and DL would it?

The formula needs to;
1) look at AE367 and realise that it contains a date of 21/03/2011
2) look at D367 and realise that it contains the symbol "€"

From this it would need to look at the table and realise that the date of the 21/03/2011 falls between the dates on row 21 and the currency corresponds with the column D (euro's). Row 21, Column D (Cell D21 = 0.8581). This is value that the forumla should reference.

Can anyone help me with a formula that would do this? Thanks in advance.

Dan

 

[T. Valko]

Here is your formula:
=INDEX($DM$17:$DN$22,MATCH($AD$367,$DK$17:$DK$22),MATCH(D$367,$DM$17:$DN$17,0))

It returns #N/A (maybe because in the last bracket you have $DM$17:$DN$17, when maybe it should be: $DM$17:$DN$22

I adapted it to:
=INDEX($DM$17:$DN$22,MATCH(AE367,$DK$17:$DK$22),MATCH(D367,$DM$17:$DN$22,0))

changing the last bracket to $DM$17:$DN$22, and instead of $AD$367, I now have AE367 (column AE is the one i want referenced).

However, the formula still returns #N/A.

This formula does not consider column DL (Date To) and would not be able to know if the date in column AE is between the dates in columns DK and DL would it?

The formula needs to;
1) look at AE367 and realise that it contains a date of 21/03/2011
2) look at D367 and realise that it contains the symbol "€"

From this it would need to look at the table and realise that the date of the 21/03/2011 falls between the dates on row 21 and the currency corresponds with the column D (euro's). Row 21, Column D (Cell D21 = 0.8581). This is value that the forumla should reference.

Can anyone help me with a formula that would do this? Thanks in advance.

Dan

This works for me...

=VLOOKUP(AE367,DK19:DN22,MATCH(D367,DK17:DN17,0))

Note that in my setup the "$" symbol equates to =CHAR(36) and the "€" symbol equates to =CHAR(128).

 

[ddub25]

Thanks, that works great. How could I get the result to return "--" if neither "€" or "$" are present in column D?

 

[Dryver14]

=if(vlookup(ae367,dk19:dn22,match(d367,dk17:dn17,0))="","--",
vlookup(ae367,dk19:dn22,match(d367,dk17:dn17,0))

 

[ddub25]

Thanks, but it does not seem to give me "--" if D367 is empty. It just returns the usual #N/A as before. What i fully would like is for it to return "--" if D367 does not equal either "€" or "$". The formula does return the correct values though as with the previous formula.

=IF(VLOOKUP(AE367,$DK$19:$DN$75,MATCH(D367,$DK$17:$DN$17,0))="","--",VLOOKUP(AE367,$DK$19:$DN$75,MATCH(D367,$DK$17:$DN$17,0)))

Any more ideas? Thanks.

Dan

 

[T. Valko]

Thanks, that works great. How could I get the result to return "--" if neither "€" or "$" are present in column D?

Can it be a value other than "€" or "$" ?