Want VLookup to Return the row above

[JoOwl0]

I have the VLOOKUP function:
=VLOOKUP(G1360,D4:G1358,COLUMNS(D1:G1))

which is properly returning the value in row G which corresponds to the
value in G1360. How do I get it to return the value in the row above
the row which has the value in G1360?

[JoOwl0]

Replace with:
I have the VLOOKUP function:
=VLOOKUP(G1360,D4:G1358,COLUMNS(D1:G1))

which is properly returning the value in some row which corresponds to
the value in G1360. How do I get it to return the value in the row
above the row which has the value in G1360?
Hanging red-faced head.....

[Niek Otten]

=INDEX(D4:G1358,MATCH(G1360,D4:G1358)-1,COLUMNS(D1:G1))

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"JoOwl0" <JoOwl@att.net> wrote in message
news:1114265954.986154.226660@z14g2000cwz.googlegroups.com...
>I have the VLOOKUP function:
> =VLOOKUP(G1360,D4:G1358,COLUMNS(D1:G1))
>
> which is properly returning the value in row G which corresponds to the
> value in G1360. How do I get it to return the value in the row above
> the row which has the value in G1360?
>

[JoOwl0]

Oops, that gives #NA.
=INDEX(D4:G1358,MATCH(G1360,D4:G1358)-1,COLUMNS(D1:G1))
But thanks anyway

[Ron Rosenfeld]

On 23 Apr 2005 07:24:55 -0700, "JoOwl0" <JoOwl@att.net> wrote:

>Replace with:
>I have the VLOOKUP function:
>=VLOOKUP(G1360,D4:G1358,COLUMNS(D1:G1))
>
>which is properly returning the value in some row which corresponds to
>the value in G1360. How do I get it to return the value in the row
>above the row which has the value in G1360?
>Hanging red-faced head.....

I don't think you can. However, you could use INDEX and MATCH to do that:

=INDEX(D4:G1358,MATCH(G1360,D4:D1358,0)-1,COLUMNS(D1:G1))


--ron

[Niek Otten]

Sorry,

=INDEX(D4:G1358,MATCH(G1360,D4:D1358)-1,COLUMNS(D1:G1))

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"JoOwl0" <JoOwl@att.net> wrote in message
news:1114267357.927728.75200@l41g2000cwc.googlegroups.com...
> Oops, that gives #NA.
> =INDEX(D4:G1358,MATCH(G1360,D4:G1358)-1,COLUMNS(D1:G1))
> But thanks anyway
>

[JoOwl0]

=INDEX(D4:G1358,MATCH(G1360,D4:D1358,0)-1,COLUMNS(D1:G1))
Thanks, Ron, it worked a charm!

[JoOwl0]

Thanks, I should have looked more carefully and seen the problem myself.

[Ron Rosenfeld]

On 23 Apr 2005 07:49:46 -0700, "JoOwl0" <JoOwl@att.net> wrote:

>=INDEX(D4:G1358,MATCH(G1360,D4:D1358,0)-1,COLUMNS(D1:G1))
>Thanks, Ron, it worked a charm!


Glad to help. Thanks for the feedback


--ron