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

 

[ddub25]

It will commonly be a "£" sign, but in future may be empty or another currency sign. Either way for now I would like "--" returned if it is not a "€" or "$" sign.

 

[T. Valko]

It will commonly be a "£" sign, but in future may be empty or another currency sign. Either way for now I would like "--" returned if it is not a "€" or "$" sign.

Try this...

=IF(OR(D367={"$","€"}),VLOOKUP(AE367,DK19:DN22,MATCH(D367,DK17:DN17,0)),"--")

 

[ddub25]

Thanks T. Valko, you've been a great help. That sorts my problem. Thanks again.

Dan

 

[T. Valko]

Thanks T. Valko, you've been a great help. That sorts my problem. Thanks again.

Dan

You're welcome. Thanks for the feedback!