Vlookup help

[Bailey031285]

I am attaching a vlookup file for g5570 I can not seem to get the vlookup to calc - any assistance would be appreciated

I have had to shrink the file due to the KB limit

[canapone]

Hi,

in G2:

Please Login or Register  to view this content.

Regards

[Pete_UK]

The cell is formatted as text, so you need to re-format as General and then double-click the cell as if to edit it then press <enter>.

Your table is defined as AG2:AN819, but the column number is set to 2 (which is a hidden column) - you might want to make this 8 instead of 2.

Also, if you intend to copy this down, then you will need to use absolute references for your table, i.e. $AG$2:$AN$819

Finally, once you have done all that your formula will return #N/A as there is no match with cell H5570 and the first column of your table - this may be something to do with you reducing the file and you really meant this to be H2 as your formula is in G2. You might also like to trap for this error using:

=IF(ISNA(your_vlookup_formula),"",your_vlookup_formula)

as you don't state which version of Excel you are using.

Hope this helps.

Pete

[Bailey031285]

Thanks for your help can you please see the attachment its like the cell wont calc. This file was sent to me in excel 2007 but i converted into excel 2003 could that be a problem?

[Pete_UK]

In the attached file I've changed the formula in G5. As you changed the lookup table to 2 columns wide, the 8 needed to be 2. Also, you needed to set the table refs to absolute, and I've changed the lookup value to H5.

But, the most important change is described in the first sentence of my earlier post - the cell was formatted as text and so the formula will just appear as a series of characters - I've changed that for you.

Now you can copy the formula down (and up) as required.

Hope this helps.

Pete