FIND A VALUE FROM CROSPONDING COLUM AND MULTIPLE CROSSPENDING ROW

  • Thread starter Thread starter Zia Butt
  • Start date Start date
Z

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"
 
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
 
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)))))),"")
 
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))
 
Back
Top