Prev: Count backwards N cells
Next: Print Dialog Box
From: Zia Butt Zia on 2 Feb 2010 08:12
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"
From: Max on 2 Feb 2010 08:34
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
--
Max
Singapore
---
"Zia Butt" wrote:
> 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"
From: Jacob Skaria on 2 Feb 2010 08:37
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


"Zia Butt" wrote:

> 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"
 | 
Pages: 1
Prev: Count backwards N cells
Next: Print Dialog Box