VLOOKUP and OFFSET
 

I have two worksheets.

SHEET1:
A B
1.1
1.2
1.3
1.4

SHEET2:
A
1.1
apples
1.2
bananas
1.4
grapes

I want a formula in Column B of sheet1 to return the values below the
corresponding value in column A of sheet2.

I wrote a lookup formula to find the corresponding value: VLOOKUP(A2,
WKSHT2!A1:A5,1,FALSE)

And I wrote an offset formula to find go down one row:
OFFSET(WKSHT2!A1,1,0,1,1)

I feel like there should be some way to replace "A1" in the second formula
with the first formula, but I cannot get it to work.

=OFFSET(VLOOKUP(A2,WKSHT2!A1:A5,1,FALSE),1,0,1,1)

The real worksheet is obviously much larger and more complicated than my
sample, but if I can just get the syntax right, I think I can work out any
other issues.
--
CG

VLOOKUP and OFFSET
 

Try this:

=INDEX(Sheet2!A2:A7,MATCH(A2,Sheet2!A2:A7,0)+1)

--
Biff
Microsoft Excel MVP


"CEG" wrote in message
...
I have two worksheets.

SHEET1:
A B
1.1
1.2
1.3
1.4

SHEET2:
A
1.1
apples
1.2
bananas
1.4
grapes

I want a formula in Column B of sheet1 to return the values below the
corresponding value in column A of sheet2.

I wrote a lookup formula to find the corresponding value: VLOOKUP(A2,
WKSHT2!A1:A5,1,FALSE)

And I wrote an offset formula to find go down one row:
OFFSET(WKSHT2!A1,1,0,1,1)

I feel like there should be some way to replace "A1" in the second formula
with the first formula, but I cannot get it to work.

=OFFSET(VLOOKUP(A2,WKSHT2!A1:A5,1,FALSE),1,0,1,1)

The real worksheet is obviously much larger and more complicated than my
sample, but if I can just get the syntax right, I think I can work out any
other issues.
--
CG

VLOOKUP and OFFSET
 

Thank you...perfect!
--
CG


"T. Valko" wrote:

Try this:

=INDEX(Sheet2!A2:A7,MATCH(A2,Sheet2!A2:A7,0)+1)

--
Biff
Microsoft Excel MVP


"CEG" wrote in message
...
I have two worksheets.

SHEET1:
A B
1.1
1.2
1.3
1.4

SHEET2:
A
1.1
apples
1.2
bananas
1.4
grapes

I want a formula in Column B of sheet1 to return the values below the
corresponding value in column A of sheet2.

I wrote a lookup formula to find the corresponding value: VLOOKUP(A2,
WKSHT2!A1:A5,1,FALSE)

And I wrote an offset formula to find go down one row:
OFFSET(WKSHT2!A1,1,0,1,1)

I feel like there should be some way to replace "A1" in the second formula
with the first formula, but I cannot get it to work.

=OFFSET(VLOOKUP(A2,WKSHT2!A1:A5,1,FALSE),1,0,1,1)

The real worksheet is obviously much larger and more complicated than my
sample, but if I can just get the syntax right, I think I can work out any
other issues.
--
CG

VLOOKUP and OFFSET
 

You're welcome!

--
Biff
Microsoft Excel MVP


"CEG" wrote in message
...
Thank you...perfect!
--
CG


"T. Valko" wrote:

Try this:

=INDEX(Sheet2!A2:A7,MATCH(A2,Sheet2!A2:A7,0)+1)

--
Biff
Microsoft Excel MVP


"CEG" wrote in message
...
I have two worksheets.

SHEET1:
A B
1.1
1.2
1.3
1.4

SHEET2:
A
1.1
apples
1.2
bananas
1.4
grapes

I want a formula in Column B of sheet1 to return the values below the
corresponding value in column A of sheet2.

I wrote a lookup formula to find the corresponding value: VLOOKUP(A2,
WKSHT2!A1:A5,1,FALSE)

And I wrote an offset formula to find go down one row:
OFFSET(WKSHT2!A1,1,0,1,1)

I feel like there should be some way to replace "A1" in the second
formula
with the first formula, but I cannot get it to work.

=OFFSET(VLOOKUP(A2,WKSHT2!A1:A5,1,FALSE),1,0,1,1)

The real worksheet is obviously much larger and more complicated than
my
sample, but if I can just get the syntax right, I think I can work out
any
other issues.
--
CG