Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Vlookup using Dashboard

    I have set up a dashboard to find the values pertaining to items in Col E (E3:E14) using a Vlookup formula based on the Branch name selected in C2. It works perfectly for BR1 , but not Br2. The details of the data are in Cols D:E

    It would be appreciated if someone could kindly amend the formula in F3:G14 so that it gives the correct value
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Howard

    ..you can't use Vlookup like that.

    See attached file for my solution using array version of Index/Match for two columns..

    zeddy
    Attached Files Attached Files

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy


    Thanks for the help. I have used this formula a while back. The only part I do not understand is the use of 1 after match



    Code:
    =INDEX(D:D,MATCH(1
    Kindly explain the significance of 1 after using match
    Last edited by RetiredGeek; 2016-01-11 at 10:24.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Howard

    ..the MATCH function is basically MATCH(what,where,how), so in..
    Code:
    =INDEX(D:D,MATCH(1,($B:$B=$C$2)*($C:$C=$E2),0))
    ..the 'where' is an array of True/False i.e 1 or 0 values (formed via the multiplier of two test conditions)
    ..the 'what' is searching for the 'true' result i.e. the "1" in the array of values.
    The returned value of the Match function in this formula will be equivalent to the row number.

    Does this explain it for you OK?

    NOTE: I repeated the method for column [G] results, but a more efficient method could be achieved (e.g. you only need to find the matching row number once for columns [F] and [G] etc etc etc)

    zeddy

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the explanation. It makes sense

Posting Permissions

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