Expanding a VLookup

[Jerry Calvacca]

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

 

[AlanSidman]

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.

 

[Jerry Calvacca]

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

 

[AlanSidman]

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.

 

[Jerry Calvacca]

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

 

[AlanSidman]

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

 

[bosco_yip]

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

 

[Peter Bartholomew]

Just to be annoying, here is a solution that few can use .

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.



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.

 

[Jerry Calvacca]

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

 

[bosco_yip]

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

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