Forum Discussion
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 c...
Jack,
=LOOKUP(9^99,INDEX(Active!R1C2:R5C6,MATCH(R1C1,Active!R1C1:R5C1,0),0))
Jack,
=LOOKUP(9^99,INDEX(Active!R1C2:R5C6,MATCH(R1C1,Active!R1C1:R5C1,0),0))
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!
You're welcome, Jack.
