Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: VlookUp
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2017
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post VlookUp

    I have a table with multiple columns. I am wanting to search one of those columns to find if there is a value > 0. If so, I want it to return back to a main table dashboard that value in one column and in the second column, it will return the number in the column it was searching. It would also rank the values with ties.

    For example:

    A B C
    Test No 1
    Stop Yes 0
    Now No 5

    Dashboard returns
    Value Total
    Now 5
    Test 1

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: VlookUp

    How about

    ABC
    2test11
    3test22
    4test30
    5test44
    6test50
    7test66
    8test77
    9test80
    10test99
    11test100

    work





    and

    AB
    1ValueTotal
    2test11
    3test22
    4test44
    5test66
    6test77
    7test99

    Lookup



    Worksheet Formulas
    CellFormula
    A2=IFERROR(INDEX(work!$A$2:$A$11,AGGREGATE(15,6,(ROW(work!$A$2:$A$11)-ROW(work!$A$2)+1)/(work!$C$2:$C$11>0),ROWS($A$2:$A2))),"")
    B2=IFERROR(INDEX(work!$C$2:$C$11,MATCH(A2,work!$A$2:$A$11,0)),"")

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Jan 2017
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VlookUp

    So for the first code, I get the expected results. The second code, I am getting 0s.

    My code is =IFERROR(INDEX($AM$52:$AM$164,MATCH(I52,$I$52:$I$164,0)),"").

    My AM Column is your C Column and My I column is your A Column.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: VlookUp

    You don't have the sheet name in that formula, are you entering the formula on the same sheet as the data?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Jan 2017
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VlookUp

    Yes

  6. #6
    Board Regular
    Join Date
    Jan 2017
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VlookUp

    Everything is on the same page. The underlying data is at the bottom of the page with the dashboard showing at the top of the page.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: VlookUp

    In that case where did you put the first formula, that I had in A2?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Jan 2017
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VlookUp

    I had it where I wanted my values from column A to go in my dashboard. Both formulas are at the top of the page.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: VlookUp

    What cell did you put it in?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Jan 2017
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VlookUp

    I put the second formula in cell AG21. The first formula for Column A was put in a merged column from AA21:AF21.

Some videos you may like

User Tag List

Tags for this Thread

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
  •