Formula to get value of column A, which has the last value in coln B

[tikku]

Hi

I would require a formula that will give me a last date which has payment.

with the above data the result would be 11-Jan-2015 as it has the last payment made of 1000006 on 11-jan-2015.

I tired the formula which give me INDEX(A:A,COUNTA(A:A)) but doesn't suffice the above condition.

<colgroup><col></colgroup><tbody>
</tbody>

Excel 2012
	A	B	C	D
1	DATE	PAYMENT
2	5-Jan-15	1,000,000.00
3	6-Jan-15	1,000,001.00
4	7-Jan-15	1,000,002.00
5	8-Jan-15	1,000,003.00
6	9-Jan-15	1,000,004.00
7	10-Jan-15	1,000,005.00
8	11-Jan-15	1,000,006.00
9	12-Jan-15
10	13-Jan-15
11	14-Jan-15
12	15-Jan-15			INDEX(A:A,COUNTA(A:A))
13	16-Jan-15			16-Jan-15
Sheet1

Cell Formulas
Range	Formula
D13	=+INDEX(A:A,COUNTA(A:A))

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307,B:B,A:A)

 

[tikku]

Thank you. Its working.

 

[Aladin Akyurek]

Thank you. Its working.

You are welcome.

 

[tikku]

Hi,

To the continuation from the above if i change the customer in Cell A2 then it should go to the sheet of customer and executes the formula =LOOKUP(9.99999999999999E+307,B:B,A:A).

For eg. i input XYZ in Cell A2 under Customer coln so the result is as below.

Excel 2012

	A	B	C
1	Customer	last payment month	last payment amount
2	XYZ	OCTOBER	1,000,006.00

<tbody>
</tbody>

FINAL​

Worksheet Formulas Cell Formula B2 =+LOOKUP(9.9E+307,XYZ!B:B,XYZ!A:A) C2 =+LOOKUP(9.9E+307,XYZ!B:B) <tbody> </tbody>

<tbody>
</tbody>

Excel 2012

	A	B
1	MONTH	PAYMENT
2	APRIL	1,000,000.00
3	MAY	1,000,001.00
4	JUNE	1,000,002.00
5	JULY	1,000,003.00
6	AUGUST	1,000,004.00
7	SEPTEMBER	1,000,005.00
8	OCTOBER	1,000,006.00
9	NOVEMBER
10	DECEMBER
11	JANUARY
12	FEBRUARY
13	MARCH

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

XYZ​

Excel 2012

	A	B
1	MONTH	PAYMENT
2	APRIL	50,000.00
3	MAY	50,001.00
4	JUNE	50,002.00
5	JULY	50,003.00
6	AUGUST	50,004.00
7	SEPTEMBER	50,005.00
8	OCTOBER	50,006.00
9	NOVEMBER
10	DECEMBER
11	JANUARY
12	FEBRUARY
13	MARCH

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

ABC​

Now if i update ABC under Customer coln replacing XYZ in Cell A2 in the final sheet then the result should be as below.

Excel 2012

	A	B	C
1	Customer	last payment month	last payment amount
2	ABC	OCTOBER	50,006.00

<tbody>
</tbody>

FINAL​

Worksheet Formulas Cell Formula B2 =+LOOKUP(9.9E+307,ABC!B:B,ABC!A:A) C2 =+LOOKUP(9.9E+307,ABC!B:B) <tbody> </tbody>

<tbody>
</tbody>

 

[Aladin Akyurek]

1) It's not 9.9E+307, rather 9.99999999999999E+307.

2) It's not =+(LOOKUP(...), rather =LOOKUP(...). That's no +.

All that said: Activate Formulas | Name Manager, enter BigNum as name in the Name box, and enter in the Refers to box:

=9.99999999999999E+307

and click OK.

B2, just enter:

=LOOKUP(BigNum,INDIRECT("'"&A2&"'!B:B"),INDIRECT("'"&A2&"'!A:A"))

C2, just enter:

=VLOOKUP(B2,INDIRECT("'"&A2&"'!A:B"),2,0)