Thread: Advanced Lookup:dynamically specify a cell.

Hi
I have 2 sheets, one sheet with a list of values and one with another set of values..

I need to create a formula to find a specific value on the second sheet, if it can find it I need to display the value 3 rows below and 2 columns to the right.

I have tried looking at Hlookup and Vlookup but they only can get a value in the same row or column as the lookup value.

I was wondering if you could dynamically specify a cell. because I can find the line number of the lookupvalue and the column will always be C - So if the lookup value is Matched at line 234 - I could create a formula that can display the value in C237?

Or any ideas?

I hope this makes any sense :-)

Thanks.

Try Index Match...

e.g

=Index('Sheet2'!A:C,Match(A1,'Sheet2'!A:A,0)+3,3)

this looks for item in A1 and finds it in column A of Sheet2, and returns what is in Column C, 3 rows below.