Excel 2007 VLOOKUP and MATCH problem [Solved/Closed]

Report
Posts
2
Registration date
Wednesday March 23, 2011
Status
Member
Last seen
March 23, 2011
-
Posts
2
Registration date
Wednesday March 23, 2011
Status
Member
Last seen
March 23, 2011
-
Hello
Long time lurker, first time poster.
I'm having an issue with a Vlookup and match function.
I have a set of data that is organised and sorted into a pivot table via macro, this in turn is copied to a new worksheet and then referenced against a vlookup and match in another sheet.
The Vlookup and Match looks up a currency, the references this against a row title to report the corresponding data

Rough layout of my data after the pivot table is inserted:
CURRENCY ClosingBalance OpeningBalance Grand Total
CHF 0 0 0
EUR 64339.14 64339.14 128678.28
GBP 0 0 0
JPY 475000 475000 950000
USD 689000 689000 1378000
ZAR 1000000 1000000 2000000
(blank)
Grand Total 2228339.14 2228339.14 4456678.28

Now, this is my Vlookup and the fields they are referencing
=IF(ISNA(VLOOKUP(F3,DATA3,MATCH(B6,Row3,0))),"0",VLOOKUP(F3,DATA3,MATCH(B6,Row3,0)))
F3 is where the currency identifier is (EUR, CHF, USD, etc.) while B6 is named OpeningBalance.

So, the cell my Vlookup is in, if the currency is EUR, it should report 64339.14.
For EUR, GBP and CHF, this Vlookup works correctly.

However, JPY, USD and ZAR seem to pull their data in from the Grand Total row for no apparent reason.

The only way to get them to report the correct data is to remove the bottom two rows. but this would need to be added to my macro, and at this point that could be quite complicated.

Any idea why I'm getting this issue with the Vlookup?
And any idea how to stop it from occuring?

1 reply

Posts
2
Registration date
Wednesday March 23, 2011
Status
Member
Last seen
March 23, 2011

Sorted it.
The Vlookups contained in the formulas were missing their 'FALSE' tags.