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

#### tikku

##### New Member
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
ABCD
1DATEPAYMENT
25-Jan-151,000,000.00
36-Jan-151,000,001.00
47-Jan-151,000,002.00
58-Jan-151,000,003.00
69-Jan-151,000,004.00
710-Jan-151,000,005.00
811-Jan-151,000,006.00
912-Jan-15
1013-Jan-15
1114-Jan-15
1215-Jan-15INDEX(A:A,COUNTA(A:A))
1316-Jan-1516-Jan-15
Sheet1
Cell Formulas
RangeFormula
D13=+INDEX(A:A,COUNTA(A:A))

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Try...

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

Thank you. Its working.

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
ABC
1Customerlast payment monthlast payment amount
2XYZOCTOBER1,000,006.00

<tbody>
</tbody>
FINAL

Worksheet Formulas
CellFormula
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
AB
1MONTHPAYMENT
2APRIL1,000,000.00
3MAY1,000,001.00
4JUNE1,000,002.00
5JULY1,000,003.00
6AUGUST1,000,004.00
7SEPTEMBER1,000,005.00
8OCTOBER1,000,006.00
9NOVEMBER
10DECEMBER
11JANUARY
12FEBRUARY
13MARCH

</tbody>
XYZ

Excel 2012
AB
1MONTHPAYMENT
2APRIL50,000.00
3MAY50,001.00
4JUNE50,002.00
5JULY50,003.00
6AUGUST50,004.00
7SEPTEMBER50,005.00
8OCTOBER50,006.00
9NOVEMBER
10DECEMBER
11JANUARY
12FEBRUARY
13MARCH

</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
ABC
1Customerlast payment monthlast payment amount
2ABCOCTOBER50,006.00

<tbody>
</tbody>
FINAL

Worksheet Formulas
CellFormula
B2=+LOOKUP(9.9E+307,ABC!B:B,ABC!A:A)
C2=+LOOKUP(9.9E+307,ABC!B:B)

<tbody>
</tbody>

<tbody>
</tbody>

Last edited:
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:
Rich (BB code):
``````
=9.99999999999999E+307
``````
and click OK.

B2, just enter:
Rich (BB code):
``````
=LOOKUP(BigNum,INDIRECT("'"&A2&"'!B:B"),INDIRECT("'"&A2&"'!A:A"))
``````

C2, just enter:
Rich (BB code):
``````
=VLOOKUP(B2,INDIRECT("'"&A2&"'!A:B"),2,0)
``````

Replies
2
Views
227
Replies
3
Views
253
Replies
19
Views
297
Replies
0
Views
227
Replies
5
Views
839

1,196,078
Messages
6,013,304
Members
441,760
Latest member
Sharina

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back