Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
JoOwl0
 
Posts: n/a
Default Want VLookup to Return the row above

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?

  #2   Report Post  
JoOwl0
 
Posts: n/a
Default

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.....

  #3   Report Post  
Niek Otten
 
Posts: n/a
Default

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"JoOwl0" wrote in message
oups.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?



  #4   Report Post  
JoOwl0
 
Posts: n/a
Default

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

  #5   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On 23 Apr 2005 07:24:55 -0700, "JoOwl0" 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


  #6   Report Post  
Niek Otten
 
Posts: n/a
Default

Sorry,

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"JoOwl0" wrote in message
ups.com...
Oops, that gives #NA.
=INDEX(D4:G1358,MATCH(G1360,D4:G1358)-1,COLUMNS(D1:G1))
But thanks anyway



  #7   Report Post  
JoOwl0
 
Posts: n/a
Default

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

  #8   Report Post  
JoOwl0
 
Posts: n/a
Default

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

  #9   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On 23 Apr 2005 07:49:46 -0700, "JoOwl0" 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup of an if statement return James Excel Worksheet Functions 2 April 6th 05 10:28 PM
Vlookup - return row no. instead of value automne Excel Discussion (Misc queries) 2 March 7th 05 01:38 AM
Can VLOOKUP return multiple answers based on several identical lo. jddtct Excel Worksheet Functions 3 January 11th 05 08:03 AM
VLookup Return Value Kevin Excel Worksheet Functions 4 December 3rd 04 04:05 PM
Can vlookup return multiple matches in a single cell? cchristensen Excel Discussion (Misc queries) 3 December 2nd 04 02:56 AM


All times are GMT +1. The time now is 12:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"