I'm trying to solve an issue for a user where they are performing a vlookup and the wookbook crashes when saving AFTER the vlookup calculation is performed. The spreadsheet is very large (70K+ rows) and the wookbook it is linking to for the lookup is bigger again (900K+ rows). It doesn't matter whether I calculate for 1 row or the whole spreadsheet, it still hangs when trying to save (there is no error message). The formula i'm using is

=VLOOKUP(B2,'[code listing.xlsx]Sheet1'!$B$1:$C$900000,2,FALSE)

Sheet looks like this:


| A | B |C | D | E | F | G|H| I |
1| - |projserial | -| - | - | - | - | -|Allocation |
2| - | 220002 |- | - | - | - | - | -|derived price/gain
3| - | 220002 |- | - | - | - | - |- |derived price/gain
4| - | 260021 |- | - | - | - | - |- |derived price cost+
5| - | 100063 |- | - | - | - | - |- |lump sum
6| - | 100063 |- | - | - | - | - |- |lump sum

(The dashed fields have data in them but are not relevant to the lookup)

The vlookup formula is in column I

The "code listing" sheet that it is looking up is like this:

| A | B | C |
1| - | 100000 | lump sum |
2| - | 100001 | lump sum |
3| - | 100002 | lump sum |
4| - | 100003 | lump sum |
5| - | 100004 | lump sum |
6| - | 100005 | lump sum |

This continues on to 900K with different values in the C column.

(Sorry about the drawings html takes all the spaces out!)

I can't work out whether its an add-in problem (crashes on multiple machines) an invalid data entry (It would take me a long time to sort through 65K+ entries!). Is the formula correct? Can I use some sort of alternative that won't crash?

Please help!

Geoff