+ Reply to Thread
Results 1 to 9 of 9

Vlookup Formula not working...

  1. #1
    Registered User
    Join Date
    12-12-2011
    Location
    Wales
    MS-Off Ver
    Excel 2003
    Posts
    4

    Vlookup Formula not working...

    Hi guys,

    Just wondering if someone could have a look at the attached workbook. I am trying to build a Holiday Booking spreadsheet and am using multiple VLookups (Not sure if its the most efficient, so that could be my problem). There are two Vlookup arrays (The first looks at the raw data and sorts it into days, the second looks at this and sorts into shifts) and the second VLookup will not display the result, just brings up "#NUM!". I have tested the search as a normal VLOOKUP and it works fine... Not sure what i am missing.

    Any help on this would be much appreciated

    Cheers

    Laz
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Vlookup Formula not working...

    Hi

    There's probably a better way to go about this (no I'm not volunteering anything), but this may get you going.

    Days!A2: =IF(ROW()-1>COUNTIF(VLookup!$B$1:$B$5,Days!$A$1),"",VLOOKUP($A$1,INDEX(Holidays,SMALL(IF($A$1=INDEX(Holidays,,1),ROW(Holidays)-MIN(ROW(Holidays))+1,""),ROW(1:1)),,1),3,FALSE))
    Days!B2: =IF(ROW()-1>COUNTIF(VLookup!$B$1:$B$5,Days!$A$1),"",VLOOKUP($A$1,INDEX(Holidays,SMALL(IF($A$1=INDEX(Holidays,,1),ROW(Holidays)-MIN(ROW(Holidays))+1,""),ROW(1:1)),,1),2,FALSE))

    Array enter in each of those cells, and copy down. You should find that A5:B9 comes back with blanks.

    Now if you check Shifts!B3 and shifts!B12, they will come back with swambol.

    As an aside, if you put
    Days!A2: =IF(ROW()-1>COUNTIF(VLookup!$B$1:$B$5,Days!$A$1),"",SMALL(IF(VLookup!$B$1:$B$5=Days!$A$1,VLookup!$D$1:$D$5,""),ROW()-1))
    also array entered, then it will bring back 08:00 rather than 08:30.

    Days!B2: =IF(ROW()-1>COUNTIF(VLookup!$B$1:$B$5,Days!$A$1),"",INDEX(VLookup!$C$1:$C$5,SUMPRODUCT(--(VLookup!$B$1:$B$5=Days!A1),--(VLookup!$D$1:$D$5=Days!G2),ROW(VLookup!$B$1:$B$5))))
    This is not array entered and should bring back the relevant item to match.

    HTH

    rylo

  3. #3
    Registered User
    Join Date
    12-12-2011
    Location
    Wales
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Vlookup Formula not working...

    PERFECT... cheers mate, been stressing over this for a couple of days now!!

    The only problem is it will only find the first 2 entries... after that it gives "#N/A", do you know why this may be?

    Cheers

    Laz
    Last edited by Swambo; 12-13-2011 at 05:29 AM. Reason: Another Error

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,516

    Re: Vlookup Formula not working...

    Pl find the attached file.Array formulas are in Days Sheet. One in A2 and other in C2 cell.You can drag.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Vlookup Formula not working...

    Swambo

    Can you update your example file with the revised formulas, and show where you are having the problem please.

    rylo

  6. #6
    Registered User
    Join Date
    12-12-2011
    Location
    Wales
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Vlookup Formula not working...

    Thanks,

    If you look on "Shifts", you can see it is only finding 2 entries, when there are clearly more than that.... Think excel has a mind of is own sometimes!!

    Thanks again

    Laz
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Vlookup Formula not working...

    Laz

    Try this in shifts!B12: =INDEX(INDEX(Day1,,2),SMALL(IF($A$12=INDEX(Day1,,1),ROW(Day1),""),ROW()-11)-1)

    Array entered. Then copy down. Not sure what is going on with the vlookup here, but this does at least work, and it suits my way of thinking

    rylo

  8. #8
    Registered User
    Join Date
    12-12-2011
    Location
    Wales
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Vlookup Formula not working...

    rylo your a genius!!

    Thanks buddy, this works perfect!!

    Until the next time excel decides not to work for no reason (which will be sooner rather than later) lol

    Thanks again

    Laz

  9. #9
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Vlookup Formula not working...

    Laz

    If this is resolved, can you please go back and mark the post as SOLVED please.

    rylo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1