Want VLookup to Return the row above

  • Thread starter Thread starter JoOwl0
  • Start date Start date
J

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?
 
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.....
 
=INDEX(D4:G1358,MATCH(G1360,D4:G1358)-1,COLUMNS(D1:G1))

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Oops, that gives #NA.
=INDEX(D4:G1358,MATCH(G1360,D4:G1358)-1,COLUMNS(D1:G1))
But thanks anyway
 
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.....
Click to expand...

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
 
Sorry,

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Back
Top