lookups

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Lookups

Lookups

resolvedResolved · Medium Priority · Version 2003

Aquilla has attended:
Excel Intermediate course

Lookups

How can you stop lookups from work ineffectively after some usage?

RE: Lookups

Hello Aquilla

Thank you for your question and welcome to the forum.

Perhaps if you could describe the problem you are experiencing with lookups in more detail, I might be able to offer some suggestions.

Kind regards
Amanda

RE: Lookups

Amanda,

After using lookup for a couple of months or so it does work as well as it could. For example what i'm looking for might be in the lookup table but it doesn't pick it up even after adding, saving and re-doing the formula. Thereby resulting in doing the code manually. I.e sorting all codes and inputting the description manually. I hope I am a bit clearer.

Many thanks,
Aquilla

RE: Lookups

Hello Aquilla

Here are a list of things to check in relation to the VLOOKUP formula you are working with:

1. Is the lookup value in the leftmost column of your table array; and is it a unique value (i.e. not repeated in the leftmost column)?

2. Does your table array include all the columns you could possibly want to extract data from?

3. Have you used the correct corresponding column index number for the column you wish to extract data from? The first column in your table is given the column index number 1, and the rest are numbered consectutively from 2 onwards in ascending order, left to right.

4. What are you using as the range lookup (true/range lookup is omitted or false)? If you require an exact match to your lookup value, use FALSE as the range lookup. If Excel is unable to find an exact match to your lookup value, it will return an NA error.

If you omit the range lookup part of the formula, or enter in TRUE then you are asking Excel to find an approximate match to your lookup value ('near enough is good enough'). In order for this to work correctly, your leftmost column in the table array (where Excel will search for your lookup value) needs to be sorted in ascending order (lowest to highest).

I hope this helps - it is difficult for me to pinpoint which of the above points are causing the problem you are having based on the information you have given me.

Kind regards
Amanda

Mon 2 Mar 2009: Automatically marked as resolved.


 

Excel tip:

Current date

CTRL+SEMICOLON

View all Excel hints and tips


Server loaded in 0.08 secs.