MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
Hope someone can help me out here!
I need a lookup forumla that returns the value 2 rows below and 8 columns to the right of the lookup_value, so like a standard vlookup, that then drops 2 rows
Any ideas?
offset function with vlookup maybe match as well
away from my desk right now. can help later if still stuck
Copy column a, insert it into column a, insert 2 cells at the top of column a
Yes, I had thought of that, but I have 8 rows to run the formula for and dont want to insert 8 columns then insert 1 to 8 rows at the top each time
Assuming your lookup reference is unique, try:
=OFFSET($A$1, MATCH(A10, A1:A4)+1, 7, 1, 1)
where A1 is the top left hand corner of your table,
where A10 is the criteria you are looking up
where a1:a4 is the array that you are testing your lookup down
where +1 = 2 rows below
and 7 = 8 columns along
1, 1 just means bring back the contents of one cell.
Brilliant, that works cheers