look up--return a value from other row instead of same row

[jk_thappeta]

1 E/W 1 2 3 6 36
2 2 4 6 96
1 2 3 6 36
5 2 3 8 240
1 5 3 9 135
543 2 1086

2 CC 1 5 4 6 120
2 2 4 5 80
3 4 2 4 96
1 2 1 4 8
304 20 6080

look for a value in the left most column of a table, and then return a value
not in the same row but from given column and other row which will be just
above a blank row i.e., in the above example given the lookup value as A1 i
should be able to extract values in G6, H6, I6 and given the lookup value as
A8 i should be able to extract values in G12, H12, I12. in the other sheet
where i have extracted these the view should be something like this

1 543 2 1086
2 304 20 6080

 

[Carl Witthoft]

That's gotta be the most incoherent post ever!

If I can decipher more or less what you want, I suggest playing around
with a combination of VLOOKUP, OFFSET, INDIRECT, and MATCH. It
will take some time to get exactly what you want, but those functions
will let you select darn near any relatively-place cell you want.