I am using nested IF commands to do searches on multiple currencies and the
VLOOKUP command to pick out the appropriate currency spot rate for a
specified date. Using dates as the lookup value it is not picking up the
correct values. Am I missing something?
Example below -
IF(G4="USD",VLOOKUP('Trading Positions'!Q4,'NZD FX Rates'!C2:E434,2)

Hi

When you omit 4th parameter, it's taken by default as TRUE and VLOOK returns
nearest match. When you have the lookup range unsorted, or sorted not by key
field, then results will be unpredictable.

To get exact match returned, set 4th parameter to FALSE (or 0), i.e.
=IF(G4="USD",VLOOKUP('Trading Positions'!Q4,'NZD FX Rates'!C2:E434,2,0)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"George" wrote in message
...
I am using nested IF commands to do searches on multiple currencies and the
VLOOKUP command to pick out the appropriate currency spot rate for a
specified date. Using dates as the lookup value it is not picking up the
correct values. Am I missing something?
Example below -
IF(G4="USD",VLOOKUP('Trading Positions'!Q4,'NZD FX Rates'!C2:E434,2)