find value one row in advance of specified value

B

broer konijn

I would like to know how I can work with a vlookup kind of function i
which I try to lookup a value that is ocurring one row above or belo
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 ro
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 a
described above. Does it exist and can someone help me?

Thanx
 
A

Allllen

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!
 
B

broer konijn

Allllen,

Intuitively I must say you could be right. My lack of knowledge usin
Index and Match functions has lead me into the help of Excel:confused
. I will figure out whether you are right, and for sure press the gree
button once the issue is solved. In the mean time I try to explain i
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 yo
like :)



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 coul
replace
that with another MATCH if you wanted.

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

thanks!
--
Allllen


broer konijn said:
I would like to know how I can work with a vlookup kind of functio in
which I try to lookup a value that is ocurring one row above o 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!
Click to expand...

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

Bob Phillips

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" <[email protected]>
wrote in message
 
Top