FIND A VALUE FROM CROSPONDING COLUM AND MULTIPLE CROSSPENDING ROW

[Zia Butt]

S.NO Name "Pay Date/Paid Date"
A B
1 Zia 01/01/2010
2 03/01/2010
3 amir 12/12/2009
4 01/01/2010

Find the value for Zia (A1) from crossponding column B but row no 2 (B2)
whch is " 03/01/2010"

 

[Max]

As-is, you could try this in E2:
=INDEX(B:B,MATCH(D2,A:A,0)+1)
where D2 contains the name, eg: Zia
Format E2 as date to taste

If you can take a layout re-design, put the 2 different dates into separate
cols. Makes things neater and easier to associate & work with.

Any joy? wave it, hit YES below

 

[Jacob Skaria]

With the query date 03/01/2010 in cell C2 try the below array formula
whichwill lookup the corresponding value from ColA.

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

=IF(ISNUMBER(MATCH(C2,B:B,0)),INDEX(A:A,MAX(IF(INDIRECT("A1:A" &
MATCH(C2,B:B,0))<>"",ROW(INDIRECT("A1:A" & MATCH(C2,B:B,0)))))),"")

 

[Jacob Skaria]

If it is the other way around..

With Zia in cell D2 try the below formula to retrieve the max date from ColB
for Zia...I assume ..

=MAX(OFFSET(INDIRECT("A" & MATCH(D2,A:A,0)),0,1,
IF(ISNA(MATCH(TRUE,INDEX(INDIRECT("A" & MATCH(D2,A:A,0)+1 & ":A1000")
<>"",),)),1000,MATCH(TRUE,INDEX(INDIRECT("A" & MATCH(D2,A:A,0)
+1 & ":A1000")<>"",),)),1))