1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Expanding a VLookup

Discussion in 'Ask an Excel Question' started by Jerry Calvacca, Feb 13, 2019.

  1. Jerry Calvacca

    Jerry Calvacca New Member

    Messages:
    9
    Hello,

    I have the following VLookup formula that works for my uses. I want to expand this now to return the value in the cell on the same column and next row, as in if this returns a value in "C3", then I want it to search to C3 and return C4. Please let me know if this does not make sense..thanks!

    =VLOOKUP(E2,Log!A:L,6,FALSE)

    Jerry
  2. AlanSidman

    AlanSidman Active Member

    Messages:
    445
    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
    Remember to desensitize the data.
  3. Jerry Calvacca

    Jerry Calvacca New Member

    Messages:
    9
    Hello Alan,
    Thanks for the feedback. I do normally attach but thought I had enough info with the copied line. You are correct though to copy a file for use. Please see the attached file

    Attached Files:

  4. AlanSidman

    AlanSidman Active Member

    Messages:
    445
    Jerry,
    I am not sure which C3 or C4. How about an example using the data you have provided and then giving the expected results. This will help to understand what you are trying to do.
  5. Jerry Calvacca

    Jerry Calvacca New Member

    Messages:
    9
    Hi Alan,

    On the file attached on the "Form Reprint" tab, I'm looking for the information logic of Row 12 (those VLOOKUPS) to also work on Row 13, 14, etc if it makes sense.

    For more clarification and testing purposes, if the value in E2 (in "Form Reprint") has multiple instances in the "Log" tab (RFQ#19028 for example) that it will automatically generate the multiple Rows of data on the "Form Reprint".

    I hope that makes sense and I appreciate the help with this!

    Best,
    Jerry

    Attached Files:

  6. AlanSidman

    AlanSidman Active Member

    Messages:
    445
    Here is a workbook that you can review and adapt to your needs.

    Attached Files:

    Thomas Kuriakose likes this.
  7. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,147
    Or..........................

    1] Tidy up your "Form Reprint" sheet by remove merge-cell column and all header description must be as same as the "Log" sheet header.

    2] In A12, copied across and down :

    =IFERROR(INDEX(Log!$A$1:$L$500,AGGREGATE(15,6,ROW($A$1:$A$500)/(Log!$A$1:$A$500=$E$2),ROWS($1:1)),MATCH(A$11,Log!$A$1:$L$1,0))&"","")

    3] Then, check E2 dropdown list to select other number for testing purpose

    Regards
    Bosco

    Attached Files:

    Last edited: Feb 14, 2019 at 8:38 AM
    Thomas Kuriakose likes this.
  8. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    647
    Just to be annoying, here is a solution that few can use :rolleyes:.

    The table is filtered twice, the first time selecting rows that match the RFQ and the second taking the first two rows from the initial selection. The formula then spills down and to the right.

    upload_2019-2-14_21-31-36.png

    N: = COUNT( FILTER(Log[RFQ], Log[RFQ]=RFQ) )
    K: = SEQUENCE(N)
    = FILTER( FILTER(LogBody, Log[RFQ]=RFQ ), k<=MIN(N,2) )


    I suspect that some of my solutions bemuse the reader so this only takes it one step further.
  9. Jerry Calvacca

    Jerry Calvacca New Member

    Messages:
    9
    First off.....THANK YOU FOR ALL OF YOUR HELP WITH THIS!!!


    Ok, here is the actual working file that I have made off of your expertise. It seems I have it all working except for on the "Form Reprint" the J Column (Special Instructions) seems to not be carrying over properly..maybe I'm missing something with the headers matching?


    Also, totally not necessary but would be a nice touch, can we force $ symbols and commas in both forms? It looks like if I try to format the cells to put commas and $ in the cells it doesn't let me do it with the searches. This refers to on "Form" column D and K. on "Form reprint" column C and H.

    Thanks again!!!!

    Best,
    Jerry

    Attached Files:

  10. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,147
    upload_2019-2-17_21-45-48.png

    1] Your "Form Reprint sheet" header I11 "Special Instuctions", the word "Instuctions" spell wrongly and should read as "Instructions"

    2] Remove " &"" " in the above post #7 formula, copied across and down :

    =IFERROR(INDEX(Log!$A$1:$L$500,AGGREGATE(15,6,ROW($A$1:$A$500)/(Log!$A$1:$A$500=$E$2),ROWS($1:1)),MATCH(A$11,Log!$A$1:$L$1,0)),"")

    then,

    copy the format from "Form" column D & K to "Form reprint" column C & H.

    Regards
    Bosco

    Attached Files:

    Last edited: Feb 17, 2019 at 1:46 PM
    Thomas Kuriakose likes this.

Share This Page