VLOOKUP (a bit quirky) - Page 2
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: VLOOKUP (a bit quirky)

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Aladins formula converts your Text (You keep referring to a General Number)to a number. It can be General or Number not both. Go back and format your lookup table as Number (just below General) and Aladin's solution should work.

  2. #12
    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 formatted the 1st column to
    number 0 decimal places(sorry about the confusion earlier) but I still have problems.



  3. #13
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 04:37, BarrettM wrote:
    I have formatted the 1st column to
    number 0 decimal places(sorry about the confusion earlier) but I still have problems.
    What did you mean by "Returns the answer sometimes but is not consistent". How are you referencing your lookup matrix/table eg is it a named range or are you using cell references. If the table is on another worksheet you can refer to the columns A:D without having to depend on where the last row of data is. There was some discussion on this topic some weeks ago and it seems that there are no overheads in using this method.

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

    Default

    I am using a named range and looking up on the same sheet

  5. #15
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have you checked the named range covers all the data?

  6. #16
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-09 04:16, BarrettM wrote:
    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.
    Try the following.

    In a clean worksheet,

    select A1:A2,
    activate Format|Cells|Text,
    and now
    in A1 enter: 100
    in A2 enter: 200

    in A3 enter: 300
    in A4 enter: 100

    in E1 enter: 100
    in E2 enter: 200
    in E3 enter: 300

    in F1 enter: Texas
    in F2 enter: California
    In F3 enter: New York

    Notice that the values in A1:A2 look like numbers, but they are not: The values in A1:A2 are just text values. The values A3:A4 look like numbers and they are numbers. The values in E1:E3 look like numbers and they are numbers.

    Now,

    in C1 enter:

    =VLOOKUP(A1,$E$1:$F$3,2,0)

    copy this down till C4.

    Examine the results and conclude.

    PS. Reformatting A1:A2 as number will not solve anything. Select A1:A4 and apply Data|Text to Columns.

    Aladin

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

    Default

    Thanks for that Aladin
    I tried out what you posted and I see what you mean.


    I think that is my problem although I have not tried it out yet. It's very interesting how the formatting does not change data that already exists.

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

    Default

      
    Meraid.
    I had a look at the lookup table. While everything looks like like it is formatted the same in the lookup column 1 it actually isnt.
    to prove this...
    1.general allign the cells in graph admin! a11:a84
    2. you will notice some cells are on the left and some on the right.
    3. this means that the values are basically in different formats still.(or corrupted)
    4. so format the cells to number/text or whatever is preferred
    5. reenter the values in those cells
    6 you will notice that as the value is reentreed the allignment will be continuous throughout the lookup.
    7. resort the list so it is in ascending order again.
    8. i tried it and it works fine now.


    all the best

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
  •  

 

 
DMCA.com