Hello all,

I need help with a formula.

I have values in cells A1 and E1:E200. In cell G1, I want to write a
formula with this logic:

Look at the value in A1, and if the A1 value matches with one of the values
in E1:E200, pick the next value in E1:E200. For example, if A1 values
matches with E100 value, pick E101 value. All those values are date values
in this format 5/22/2009.

I tried to use this formula in G1, and it didn't work.

=offset(vlookup(A1,$E$1:$E$200,1,false),1,0)

Please help. Thanks.

Accesshelp wrote:
Hello all,

I need help with a formula.

I have values in cells A1 and E1:E200. In cell G1, I want to write a
formula with this logic:

Look at the value in A1, and if the A1 value matches with one of the values
in E1:E200, pick the next value in E1:E200. For example, if A1 values
matches with E100 value, pick E101 value. All those values are date values
in this format 5/22/2009.

I tried to use this formula in G1, and it didn't work.

=offset(vlookup(A1,$E$1:$E$200,1,false),1,0)

Please help. Thanks.

You're close. VLOOKUP returns the value. What you want to feed to the
final lookup is the position of the value. MATCH will do it. Here are
two ways:

=OFFSET(E1,MATCH(A1,$E$1:$E$29,0),0)

=INDEX($E$1:$E$29,MATCH(A1,$E$1:$E$29,0)+1)

In general folks will tell you option 2 is better since it does not
involve volatile functions.

=OFFSET(E1,MATCH(A1,E1:E200,0),0)

Your use of VLOOKUP() returns the value and not the position.
--
Gary''s Student - gsnu200854


"Accesshelp" wrote:

Hello all,

I need help with a formula.

I have values in cells A1 and E1:E200. In cell G1, I want to write a
formula with this logic:

Look at the value in A1, and if the A1 value matches with one of the values
in E1:E200, pick the next value in E1:E200. For example, if A1 values
matches with E100 value, pick E101 value. All those values are date values
in this format 5/22/2009.

I tried to use this formula in G1, and it didn't work.

=offset(vlookup(A1,$E$1:$E$200,1,false),1,0)

Please help. Thanks.

smartin wrote:
Accesshelp wrote:
Hello all,

I need help with a formula.
I have values in cells A1 and E1:E200. In cell G1, I want to write a
formula with this logic:

Look at the value in A1, and if the A1 value matches with one of the
values in E1:E200, pick the next value in E1:E200. For example, if A1
values matches with E100 value, pick E101 value. All those values are
date values in this format 5/22/2009.

I tried to use this formula in G1, and it didn't work.

=offset(vlookup(A1,$E$1:$E$200,1,false),1,0)

Please help. Thanks.

You're close. VLOOKUP returns the value. What you want to feed to the
final lookup is the position of the value. MATCH will do it. Here are
two ways:

=OFFSET(E1,MATCH(A1,$E$1:$E$29,0),0)

=INDEX($E$1:$E$29,MATCH(A1,$E$1:$E$29,0)+1)

In general folks will tell you option 2 is better since it does not
involve volatile functions.

I should have mentioned I demonstrated a smaller range in column E than
you specified.

smartin,

Thank you very much for your helps. Work perfectly! You are genius!

"smartin" wrote:

smartin wrote:
Accesshelp wrote:
Hello all,

I need help with a formula.
I have values in cells A1 and E1:E200. In cell G1, I want to write a
formula with this logic:

Look at the value in A1, and if the A1 value matches with one of the
values in E1:E200, pick the next value in E1:E200. For example, if A1
values matches with E100 value, pick E101 value. All those values are
date values in this format 5/22/2009.

I tried to use this formula in G1, and it didn't work.

=offset(vlookup(A1,$E$1:$E$200,1,false),1,0)

Please help. Thanks.

You're close. VLOOKUP returns the value. What you want to feed to the
final lookup is the position of the value. MATCH will do it. Here are
two ways:

=OFFSET(E1,MATCH(A1,$E$1:$E$29,0),0)

=INDEX($E$1:$E$29,MATCH(A1,$E$1:$E$29,0)+1)

In general folks will tell you option 2 is better since it does not
involve volatile functions.

I should have mentioned I demonstrated a smaller range in column E than
you specified.

Gary's,

Thank you very much for your helps. Work perfectly! You are genius!

"Gary''s Student" wrote:

=OFFSET(E1,MATCH(A1,E1:E200,0),0)

Your use of VLOOKUP() returns the value and not the position.
--
Gary''s Student - gsnu200854


"Accesshelp" wrote:

Hello all,

I need help with a formula.

I have values in cells A1 and E1:E200. In cell G1, I want to write a
formula with this logic:

Look at the value in A1, and if the A1 value matches with one of the values
in E1:E200, pick the next value in E1:E200. For example, if A1 values
matches with E100 value, pick E101 value. All those values are date values
in this format 5/22/2009.

I tried to use this formula in G1, and it didn't work.

=offset(vlookup(A1,$E$1:$E$200,1,false),1,0)

Please help. Thanks.