Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
=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
|
|||
|
|||
Oops, that gives #NA.
=INDEX(D4:G1358,MATCH(G1360,D4:G1358)-1,COLUMNS(D1:G1)) But thanks anyway |
#5
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
=INDEX(D4:G1358,MATCH(G1360,D4:D1358,0)-1,COLUMNS(D1:G1))
Thanks, Ron, it worked a charm! |
#8
|
|||
|
|||
Thanks, I should have looked more carefully and seen the problem myself.
|
#9
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup of an if statement return | Excel Worksheet Functions | |||
Vlookup - return row no. instead of value | Excel Discussion (Misc queries) | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions | |||
VLookup Return Value | Excel Worksheet Functions | |||
Can vlookup return multiple matches in a single cell? | Excel Discussion (Misc queries) |