Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: VLOOKUP (a bit quirky)

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Northern Ireland
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a table with 4 columns.
    Col 1 has the numbers that I wish to search through, (This is in ascending order and is formatted as a general number).
    Col 3 has text which I want to appear when the VLOOKUP returns an exact match.

    I am using false

    The problem is that VLOOKUP returns the answer sometimes but for some reason is not consistent.

    Has anyone come across this problem before? and if so how could I resolve it?



    [ This Message was edited by: barrettm on 2002-04-09 00:57 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    make sure the last bit of the vlookup statement is set to FALSE

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    the FALSe statemnt is normally what gives the error.
    also make sure the loojup covers all the relevant cells, and that formatting is consistent.
    and make sure the 1st column is sorted correctly and there should be no problems at all.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-09 00:17, BarrettM wrote:
    I have a table with 4 columns.
    Col 1 has the numbers that I wish to search through, (This is in ascending order and is formatted as a general number).
    Col 3 has text which I want to appear when the VLOOKUP returns an exact match.

    I am using false

    The problem is that VLOOKUP returns the answer sometimes but for some reason is not consistent.

    Has anyone come across this problem before? and if so how could I resolve it?

    [ This Message was edited by: barrettm on 2002-04-09 00:57 ]
    I don't know what happened to the reply that I wanted to post!

    The first column of your lookup table must be really number-formatted, if the lookup value that you feed to your VLOOKUP formula is a number.

    Try:

    =VLOOKUP(A1+0,lookup-table,3,0)

    where A1 is your lookup-value. Adjust to suit. If this leads to the expected results, then the first column of your lookup-table is not numeric but text-formatted.



    [ This Message was edited by: Aladin Akyurek on 2002-04-09 01:17 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok.
    is the lookup only in one cell or multiple cells?

    are the errors only occuring in ceratin lookups,

    are the lookups all the same lookup matrix?

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Northern Ireland
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 01:05, Qroozn wrote:
    ok.
    is the lookup only in one cell or multiple cells?

    are the errors only occuring in ceratin lookups,

    are the lookups all the same lookup matrix?
    The lookup formula is in one cell.
    The errors are occuring for particular lookups.
    I don't understand what you mean by lookup matrix.

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    lookup matrix is the area of the lookups. but is not the problem if you only have one lookup.

    can you send me the sheet or not?

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Northern Ireland
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I can send you the workbook, it was created in excel 97.

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok. send it to me.
    i do alot of work with lookups
    and have 97 running.

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Northern Ireland
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 00:17, BarrettM wrote:
    I have a table with 4 columns.
    Col 1 has the numbers that I wish to search through, (This is in ascending order and is formatted as a general number).
    Col 3 has text which I want to appear when the VLOOKUP returns an exact match.

    I am using false

    The problem is that VLOOKUP returns the answer sometimes but for some reason is not consistent.

    Has anyone come across this problem before? and if so how could I resolve it?

    The first column of your lookup table must be really number-formatted, if the lookup value that you feed to your VLOOKUP formula is a number.

    Try:

    =VLOOKUP(A1+0,lookup-table,3,0)

    where A1 is your lookup-value. Adjust to suit. If this leads to the expected results, then the first column of your lookup-table is not numeric but text-formatted.

    Aladin, Does the look upvalue have to formatted to number as I am looking up values such as
    018
    038
    048
    etc?

    My 1st column is formatted to General number.




Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •