I would like to know how I can work with a vlookup kind of function in
which I try to lookup a value that is ocurring one row above or below
the 'specified value'.

Example:

I have a list of data and values

01/01/2000 value x
02/01/2000 value y

Now I would like to make a lookup for the value of the cell one row
above 02/01/2000, so it returns value x.

Sollutions cannot be:
-specify directly on the 01/01/2000 row
-cannot also use the date-1

Reasons are quite complicated to explain, but it should work as
described above. Does it exist and can someone help me?

Thanx!


--
broer konijn
------------------------------------------------------------------------
broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402
View this thread: http://www.excelforum.com/showthread...hreadid=551328

Broer,

Don't use vlookup. Use Index and Match.

=INDEX(A1:B4,MATCH(D1,A:A,0)-1,2)

* A1:B4 is your data table
* D1 is the value that you are looking for
* AA tells it to look for the value of D1 in column A
* the -1 means look in the row above
* 2 means take the result from the 2nd column of data (you could replace
that with another MATCH if you wanted.

Please give me a green tick (correct answer) if this is right. I am trying
to score points (only 3 so far).

thanks!
--
Allllen


"broer konijn" wrote:


I would like to know how I can work with a vlookup kind of function in
which I try to lookup a value that is ocurring one row above or below
the 'specified value'.

Example:

I have a list of data and values

01/01/2000 value x
02/01/2000 value y

Now I would like to make a lookup for the value of the cell one row
above 02/01/2000, so it returns value x.

Sollutions cannot be:
-specify directly on the 01/01/2000 row
-cannot also use the date-1

Reasons are quite complicated to explain, but it should work as
described above. Does it exist and can someone help me?

Thanx!


--
broer konijn
------------------------------------------------------------------------
broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402
View this thread: http://www.excelforum.com/showthread...hreadid=551328

Allllen,

Intuitively I must say you could be right. My lack of knowledge using
Index and Match functions has lead me into the help of Excel
. I will figure out whether you are right, and for sure press the green
button once the issue is solved. In the mean time I try to explain it
using an example which I added. You can look at that one if you like.

PS: I have posted some othe brainteasers, try to score points if you
like :)




Allllen Wrote:
Broer,

Don't use vlookup. Use Index and Match.

=INDEX(A1:B4,MATCH(D1,A:A,0)-1,2)

* A1:B4 is your data table
* D1 is the value that you are looking for
* AA tells it to look for the value of D1 in column A
* the -1 means look in the row above
* 2 means take the result from the 2nd column of data (you could
replace
that with another MATCH if you wanted.

Please give me a green tick (correct answer) if this is right. I am
trying
to score points (only 3 so far).

thanks!
--
Allllen


"broer konijn" wrote:


I would like to know how I can work with a vlookup kind of function
in
which I try to lookup a value that is ocurring one row above or
below
the 'specified value'.

Example:

I have a list of data and values

01/01/2000 value x
02/01/2000 value y

Now I would like to make a lookup for the value of the cell one row
above 02/01/2000, so it returns value x.

Sollutions cannot be:
-specify directly on the 01/01/2000 row
-cannot also use the date-1

Reasons are quite complicated to explain, but it should work as
described above. Does it exist and can someone help me?

Thanx!


--
broer konijn

------------------------------------------------------------------------
broer konijn's Profile:
http://www.excelforum.com/member.php...o&userid=34402
View this thread:
http://www.excelforum.com/showthread...hreadid=551328




+-------------------------------------------------------------------+
|Filename: Book3.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4879 |
+-------------------------------------------------------------------+

--
broer konijn
------------------------------------------------------------------------
broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402
View this thread: http://www.excelforum.com/showthread...hreadid=551328

MATCH looks for a value in a range, and returns the index number of the
found item. This can be passed to the INDEX function to find a corresponding
cell in another range, similar to what VLOOKUP does. But if you subtract 1
from the value return by MAX, you can find the value above in the same
range.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"broer konijn"
wrote in message
news:broer.konijn.29c7m2_1150196407.6271@excelforu m-nospam.com...

Allllen,

Intuitively I must say you could be right. My lack of knowledge using
Index and Match functions has lead me into the help of Excel
I will figure out whether you are right, and for sure press the green
button once the issue is solved. In the mean time I try to explain it
using an example which I added. You can look at that one if you like.

PS: I have posted some othe brainteasers, try to score points if you
like :)




Allllen Wrote:
Broer,

Don't use vlookup. Use Index and Match.

=INDEX(A1:B4,MATCH(D1,A:A,0)-1,2)

* A1:B4 is your data table
* D1 is the value that you are looking for
* AA tells it to look for the value of D1 in column A
* the -1 means look in the row above
* 2 means take the result from the 2nd column of data (you could
replace
that with another MATCH if you wanted.

Please give me a green tick (correct answer) if this is right. I am
trying
to score points (only 3 so far).

thanks!
--
Allllen


"broer konijn" wrote:


I would like to know how I can work with a vlookup kind of function
in
which I try to lookup a value that is ocurring one row above or
below
the 'specified value'.

Example:

I have a list of data and values

01/01/2000 value x
02/01/2000 value y

Now I would like to make a lookup for the value of the cell one row
above 02/01/2000, so it returns value x.

Sollutions cannot be:
-specify directly on the 01/01/2000 row
-cannot also use the date-1

Reasons are quite complicated to explain, but it should work as
described above. Does it exist and can someone help me?

Thanx!


--
broer konijn

------------------------------------------------------------------------
broer konijn's Profile:
http://www.excelforum.com/member.php...o&userid=34402
View this thread:
http://www.excelforum.com/showthread...hreadid=551328




+-------------------------------------------------------------------+
|Filename: Book3.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4879 |
+-------------------------------------------------------------------+

--
broer konijn
------------------------------------------------------------------------
broer konijn's Profile:
http://www.excelforum.com/member.php...o&userid=34402
View this thread: http://www.excelforum.com/showthread...hreadid=551328