combination of =OFFSET() and =ADDRESS() formulas with =VLOOKUP() formula

[okcsteve]

I have a =VLOOKUP() formula that returns a value on a separate worksheet. That formula reads =VLOOKUP(G4,'Worksheet 2'!A$71:N$410,14,FALSE).

Now I need a formula that returns the value in the cell located immediately below the cell that is returned by that formula.

I believe I need some combination of the ADDRESS and OFFSET formula with the VLOOKUP formula.

[JohnTopley]

try

=INDEX(N$71:N$410,MATCH(VLOOKUP(G4,Worksheet 2!A$71:N$410,14,FALSE),N$71:N$410,0)+1)

[okcsteve]

John - I had to add Worksheet 2! to the first and last range and then the formula worked beautifully. You set me on the right track. Thank you!!!

[okcsteve]

John - at first I thought your formula worked but I noticed later that it does not and it is because the values repeat and the formula simply looks for the first value that matches. That causes many of the values to be wrong because it repeats the offset from that first value. I need for the formula to retrieve the cell reference of the vlookup result and return the value located in the cell immediately below that cell reference. Thoughts? Thanks.

[JohnTopley]

VLOOKUP and MATCH retrieve the FIRST value that matches the selection criterion so if there are multiple occurrences of a value then I am not sure what the solution is. Could you please post a small sample file showing the problem?

[okcsteve]

John - I figured out a bit of an awkward work-around but it saved time over figuring out how to use reference formulas. Thank you very much for your help on this.

[Jonmo1]

I would go with

=INDEX('Worksheet 2'!N$72:N$411,MATCH(G4,'Worksheet 2'!A$71:A$410,0))

Notice the offset by 1 row in the ranges.

[okcsteve]

Thanks for the idea. I solved it another way but the problem with INDEX and MATCH is that they look for the first value that matches and I have repeating values. That causes wrong answers because is goes to the first match and yields the offset from that, when the offset may be a different value if it went to the correct match instead of the first match. I'm good now though.

[JohnTopley]

Thank you for the feedback: glad you found a "workaround".