Lookup or?

[George]

Dear friends,

I have a worksheet with 4 columns, i.e. in Column1 I record the date,
Column2-4 I record the exchange rates in JPY, GBP and USD.

I need, in a different worksheet to enter the date and Currency and to:
1. Show me in a nearby cell the exchange rate of that date and currency and
2. To highlight the corresponding date and exchange rate of that currency in
the first worksheet.

Shall I need some coding or?

Any ideas will be highly appreciated.

Thanking you in advance,

GeorgeCY

 

[Jacob Skaria]

--If the data is arranged in this format in Sheet1

Dates JPY GBP USD
8/5/2009 x x x
9/5/2009 x x x
10/5/2009 x x x

In Sheet2 C1
A1 = Query Date
B1 = exch rate (example USD)
=VLOOKUP(A1,Sheet1!A,MATCH(B1,A11,0),0)

--For highlighting you can try Conditional Formatting

Create named ranges (menu Insert>Name>Define)
qDate = Query Date = Sheet2 A1
qExc = Exch Rate = Sheet2 A2

1. Select the cell/Range (Sheet1 A).
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=AND(INDEX($A:$D,1,COLUMN())=qExc,INDEX($A:$D,ROW(),1)=qDate)

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

If this post helps click Yes

 

[Jacob Skaria]

Correction for VLOOKUP:

In Sheet2 C1
A1 = Query Date
B1 = exch rate (example USD)
=VLOOKUP(A1,Sheet1!A,MATCH(B1,Sheet1!A11,0),0)

If this post helps click Yes