Consulting

Results 1 to 13 of 13

Thread: Vlookup Help

  1. #1
    VBAX Tutor CCkfm2000's Avatar
    Joined
    May 2005
    Posts
    209
    Location

    Vlookup Help

    I need help with vlookup.

    I have a spreadsheet with the following information I need to key in

    In cell F2 I need to enter Total Hours 8
    In cell F3 I need to enter Total Plts 104
    In cell F4 I need to enter P/P Hour 13
    In cell F5 I need to enter Start Time 7

    I can have up to 5 different entries in the spreadsheet any one time.

    The problem is when I do a vlookup in cells N9 to O49
    I should get the following

    N9 = Monday Day O9 = 09:00
    N10 = Monday Day O10 = 11:00
    N11 = Monday Day O11 = 13:00
    N12= Monday Day O12 = 15:00

    But I get in cell N12 = Friday Night, O12 = 06:00

    The vlookup in column B need to get ever 26th value.


    Here is some more data to enter for example

    Line A Line B Line C Line D Line E
    Total Hours 83 37 36 8 0
    Total Plts 149 124 119 104 0
    P/P Hour 2 3 3 13 0
    Start Time 41 37 91 7 0


    i've attached a test file for info.

    Any help will much appreciated

    Many Thanks

  2. #2
    BoardCoder
    Licensed Coder
    VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    Change your formula to:

    =VLOOKUP(M9,$B$9:$E$128,4,FALSE)
    i.e. use false at the end. The entries below row 4 are then N/A's as 130+ are never found but you could test for this and display something else using iserror() if you wanted to.

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  3. #3
    VBAX Tutor CCkfm2000's Avatar
    Joined
    May 2005
    Posts
    209
    Location
    Thanks mark007 for you quick response, having changed the formula to the you suggested and entering the following

    Line A Line B
    83 37
    149 124
    2 3
    41 37

    I get in cell N 9 #N/A O 9 #N/A
    cell N 10 is ok
    cell N 11 i get #N/A


    thanks

  4. #4
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    Well in that case 26 isn't found, what would you want it to return?
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  5. #5
    VBAX Mentor ALe's Avatar
    Joined
    Aug 2005
    Location
    Milan
    Posts
    383
    Location
    I didn't get exactly your problem but maybe this could be of help.
    In the lookup formula change the last argument from TRUE to FALSE. I did it and I got the results you expected.

  6. #6
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    That won't work as that's what he had originally (it defaults to true).

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  7. #7
    VBAX Mentor ALe's Avatar
    Joined
    Aug 2005
    Location
    Milan
    Posts
    383
    Location
    Yes Mark007. Now I got the problem and I see you're right. Thank you. This has been the chance to understand the meaning of that TRUE/FALSE in the VLookUp formula (I never looked up it).

  8. #8
    VBAX Tutor CCkfm2000's Avatar
    Joined
    May 2005
    Posts
    209
    Location
    thanks for your help on this...

    i need to lookup 26, 52 and on so.
    e.g if i can't get 26 then i need to get the next close number equaling 26 up or down.

    give or take a few...

  9. #9
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    So it can be either up or down. Will have to think about that one!
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  10. #10
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    Ok, try this array formula approach, think it just about does the job though I'm sure there must be a neater approach!

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  11. #11
    VBAX Tutor CCkfm2000's Avatar
    Joined
    May 2005
    Posts
    209
    Location
    thanks mark007... your a star. looks like you have crack this problem

    i've been at this for about 2 weeks now..

    thanks for all the help to everybody.

    i'm going to mark this as solved now.....

    cckfm2000

  12. #12
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    No problem. If you come across any scenarios with problems, post back and I'll take another look!

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  13. #13
    VBAX Tutor CCkfm2000's Avatar
    Joined
    May 2005
    Posts
    209
    Location
    thanks again mark007

Posting Permissions

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