Search code examples
excelcopying

Excel Data Copying

I have two columns like the list below in a worksheet.

What I'd like to do is pull the value from the second column but only if the corresponding 1st column value matches one that I specify. This I could do with a Vlookup however I'd also like to be able to specify the row number from the array that the 1st column matches.

For example, if I set the value to '2' and the row number to '2' it would give me the value 14 since it's matched the first value in column 1 and created an array then given me the second row of that array which is 14.

Any ideas?

1   10
1   11
1   12
2   13
2   14
2   15
3   16
3   17
3   18
Solution

  • If your data is sorted as you have it in the example, ie. by first column ascending, then second column ascending. Then you can use a combination of OFFSET(), INDEX() and MATCH().

    =OFFSET(INDEX(second_column,MATCH(lookup_a,first_column,0)),entry_row-1,0)

    This first acquires the cell address with INDEX(..., MATCH()) of the first cell matching your lookup value, then the OFFSET() function knocks that down by how many rows you want within that group. Of course this entirely depends on the sorting being as described.

    Lookup two rows with <code>OFFSET()</code>

    If you wanted the kth-smallest entry in an unsorted second column (but still grouped on the first column), then you could use:

    =SMALL(OFFSET(second_column,MATCH(lookup_a,first_column,0)-1,0,MATCH(lookup_a,first_column,1)-MATCH(lookup_a,first_column,0)+1,1),entry_row)

    This has the advantage of returning #N/A if you move out of bounds on your selection. Surrounding it with IFERROR(...., "").

    Alternatively, using the first method for unsorted second column data and just checking the offset to see if it retains the lookup value, you can use:

    =IF(OFFSET(OFFSET(INDEX(second_column,MATCH(lookup_a,first_column,0)),entry_row-1,0),0,-1)=lookup_a,OFFSET(INDEX(second_column,MATCH(lookup_a,first_column,0)),entry_row-1,0),"")