Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VLookup in Array Formula (XL2KSR1)

    I'm trying to use VLOOKUP in an array formula, however it doesn't work right. My questions are: Can it be made to work, if so, how? if not, what alternative is there to this approach. Please see attachment

    Thanks,
    Ken
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: VLookup in Array Formula (XL2KSR1)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Ken

    Did you try and use a Custom Made, User Defined VBA function for such a thing?

    I think this would be your best best.

    Let me know if you need any help with VBA, for now I'll keep hacking at this until I find a Better solution for you.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup in Array Formula (XL2KSR1)

    ken
    I suspect that it has become way too complex for easy analysis.
    My assumption is that VLOOKUP decides that (even with array formulae) its job is to find the first item and then stop.

    I had difficulty understanding why it had to be done in quite that way.
    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> What was wrong with doing the VLOOKUPS in cells B8:B13 and then using the resulting single answers in a simple construct? It would certainly be easier to debug.

    I enjoyed discovering your method of rangenaming CCs - nice one (I always wondered how to make robust definitions) <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>

    Andrew O

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VLookup in Array Formula (XL2KSR1)

    I agree with Andrew, and to detail what he alluded to:

    In B8:B13 you would have the lookups:
    =VLOOKUP(A8,CCs,2,0)
    Name B8:B13 LookupMyList
    and then use as ARRAY formula:
    =SUM(IF(ISERROR(LookupMyList),0,IF(LookupMyList=$A $1,0,1)))

    It adds an extra column, but that shouldn't be a big deal.

    You could even get rid of the array formula by using in B8:B13:
    =VLOOKUP(A8,CCs,2,0)<>$A$1

    and using the formula:
    =COUNTIF(LookupMyList,TRUE)

    I think that the problem is (as you noticed) that VLOOKUP is NOT an ARRAY formula. The command:
    VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)

    Assumes that lookup_value is a single value. If it is a range, it uses the top-left value of the range. Even if this is ARRAY entered it ALWAYS and ONLY looks at the TopLeft value.

    Steve

  5. #5
    New Lounger
    Join Date
    Nov 2001
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup in Array Formula (XL2KSR1)

    I added the following in cells B8:B13 (next to MyList range):

    =IF(NOT(ISERROR(SEARCH(A$1,A8))),1,0) It seems to work OK. See attached file.

    I could not get it to work as an array.

    Brian
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup in Array Formula (XL2KSR1)

    =SUMPRODUCT((ISNUMBER(MATCH(Table!A2:A84,MyList,0) ))*(Table!B2:B84=A1))

    to be entered in A3 in Sheet1.

    Aladin
    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup in Array Formula (XL2KSR1)

    Thanks to all who answered my post. Adding an additional column is not an option that I wish to use because this example is cut down from a form that I use. To start adding column(s) would require a lot of reworking of macros and the form itself (although I suppose I could use a hidden column). Anyway Aladin nailed it for me. Thanks Aladin. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    Ken

Posting Permissions

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