Vlookup + 1

[azidrane]

What I need to do is this. I have a table that i do a look up on that
looks like this.



Name1 |data1|data2| | |data1|data2
Name2 |data1|data2|data1|data2| |

The lookup grabs the data1 that is furthest to the right (its a date).
What I then need to do is do a lookup to grab the data2 (a percentage)
that is to the right of the data1.

Is there someway I can get the cell address returned in my first
function and then just increment the culumn being addressed by one to
return the data2?

 

[NIMISH]

Hint is use INDIRECT function. This function converts text string to
range name.

For instance cell A1 contains "END" then

VLOOKUP(Lookup_Value,INDIRECT(A1),ColumnOffset,TRUE(or FALSE)) will
look for data from the named range END.

If value of A1 changes to "TOP" then above function will look for data
from named range TOP.

I am sure you know how to define named range.

Is it what are you looking for? If not then send me fraction of your
spread sheet and I may be able to help you in better way.

Nimish

 

[Bob Phillips]

Why not change the lookup to grab one column further on?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[azidrane]

I'd love to, but the lookup is based on the values in the row's its
looking in. Its an array formula that looks like this:
=MAX(VLOOKUP(H5,Coach!A3:N188,{3,5,7,9,11,13},FALSE))