Forum Discussion

Jack Miller's avatar
Jack Miller
Copper Contributor
Jan 28, 2018
Solved

Get last column of data from second worksheet

Sheet1

sdf 1 <-desired results from formula

xcv 3 <-

wer 9 <-

 

"Active" sheet

wer 1 3 5 7 9

sdf 2 4 6 8 1

xcv 3 4 2 3

tyu 5 7 9

ghj 3 2 4 5 6

 

This gets the data if the column is known = 80

=VLOOKUP(RC1,Active!R1C1:R200C1000,80,FALSE)

 

This finds the row number

=MATCH(RC1,Active!C1,0)

 

This gets the last column number, but the row must be known = 130

=LOOKUP(2,1/(Active!R130C17:R130C1000<>""),COLUMN(Active!R130C17:R130C1000))

 

This contains an error and I cannot find it (Maybe needs  ' and ", I've tried everything that makes sense to me)

=LOOKUP(2,1/(Active!R&MATCH(RC1,Active!C1,0)&C17:R&MATCH(RC1,Active!C1,0)&C1000<>""),COLUMN(Active!R&MATCH(RC1,Active!C1,0)&C17:R&MATCH(RC1,Active!C1,0)&C1000))

 

I believe this finds the row in the second sheet and gets the data but it has an error

=VLOOKUP(RC1,Active!R1C1:R200C1000,LOOKUP(2,1/(Active!R&MATCH(RC1,Active!C1,0)&C17:R&MATCH(RC1,Active!C1,0)&C1000<>""),column(Active!R&MATCH(RC1,Active!C1,0)&C17:R&MATCH(RC1,Active!C1,0)&C1000)),FALSE)

 

Thanks for your help!

Jack

Formulas & Functions
Need Help
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Jan 29, 2018

    Jack,

     

    =LOOKUP(9^99,INDEX(Active!R1C2:R5C6,MATCH(R1C1,Active!R1C1:R5C1,0),0))

3 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Jan 29, 2018

    Jack,

     

    =LOOKUP(9^99,INDEX(Active!R1C2:R5C6,MATCH(R1C1,Active!R1C1:R5C1,0),0))
    • Jack Miller's avatar
      Jack Miller
      Copper Contributor
      Jan 29, 2018

      Thanks Detlef!

      I appreciate your help. Not to be critical but for others who may want to use this formula, the working formula is -

      =LOOKUP(9^99,INDEX(Sheet2!R1C2:R5C6,MATCH(RC1,Sheet2!R1C1:R5C1,0),0))

       

      To help understand these steps -

      A modified Active sheet -

      wer 1 3 5 7 9
      sdf   4 6 8 1
      xcv     2 3  
      tyu   7 9    
      ghj 3 2 4 5 6

       

      The steps of the formula broken out -

        Result  
      sdf 2 =MATCH(RC1,Sheet2!R1C1:R5C1,0)
      xcv 3 =MATCH(RC1,Sheet2!R1C1:R5C1,0)
      wer 1 =MATCH(RC1,Sheet2!R1C1:R5C1,0)
           
      sdf 0 =INDEX(Sheet2!R1C2:R5C6,MATCH(RC1,Sheet2!R1C1:R5C1,0),0)
      xcv 0 =INDEX(Sheet2!R1C2:R5C6,MATCH(RC1,Sheet2!R1C1:R5C1,0),0)
      wer 1 =INDEX(Sheet2!R1C2:R5C6,MATCH(RC1,Sheet2!R1C1:R5C1,0),0)
           
      sdf 1 =LOOKUP(9^99,INDEX(Sheet2!R1C2:R5C6,MATCH(RC1,Sheet2!R1C1:R5C1,0),0))
      xcv 3 =LOOKUP(9^99,INDEX(Sheet2!R1C2:R5C6,MATCH(RC1,Sheet2!R1C1:R5C1,0),0))
      wer 9 =LOOKUP(9^99,INDEX(Sheet2!R1C2:R5C6,MATCH(RC1,Sheet2!R1C1:R5C1,0),0))

      Previously, I gave up on the INDEX because it doesn't seem to work, but plug it into the LOOKUP, and behold! This is how I've learned to build up formula, but when a step doesn't work, I get stuck.  Thanks again for your help, Detlef!

Resources