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))
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.