How to extract last entry in a column from another sheet using Index, Match?

[Yodelayheewho]

Hello,

Attached is an example. Sheet 1 is a checkbook register and budget. Columns B through G are checkbook register. Columns I through AJ are the funding categories. Column H sums the Balance for each category (BAL_GAS+BAL_HOU+BAL_INS....etc) to make sure the category balances align with the checkbook balance in Column G.

I've created Sheet 2 so I can see a simple summary of the balance for each budget item. For example, I would like to see what my balance is for gas money, etc.

In my mind, I need to do an Index/Match to match the Column Headers on Sheet 1, with the Column Header names listed on Sheet 2 in Column B. Once these match, then I need to extract the last entry of that column.

Thank you in advance for your help!

 

[p45cal]

Like this? Right-click the new table and choose Refresh.

 

[Yodelayheewho]

When I click refresh, I get an error message:
Initialization of the data source failed.
Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database.
I don't see any formulas.

 

[Yodelayheewho]

I'm running Microsoft Office Professional Plus 2013. I did just enable the PowerPivot add-in to see if that helped and no luck.

 

[p45cal]

Ahh.
It's Power Query, see:
Install Power Query With Excel 2013 | MyExcelOnline

Install Power Query Excel 2013 | MyExcelOnline

Power Query in Excel can be used to access, clean and transform all that messy data and displays it in a way that Excel can work with. Click here to learn more about Power Query Excel 2013!

www.myexcelonline.com

Download Microsoft Power Query for Excel from Official Microsoft Download Center

Download Microsoft Power Query for Excel from Official Microsoft Download Center

Microsoft Power Query for Excel is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, access and collaboration. (Note: The Microsoft Power Query add-in for Excel 2010/2013 is no longer being updated by Microsoft. Please upgrade...

www.microsoft.com

The first link has an annoying popup which covers the screen but this can be dismissed with the Esc key on the keyboard.

 

[Yodelayheewho]

Yikes! I just stepped into another world. I have no idea how this is done...and I thought I was a power Excel user.

 

[bosco_yip]

Try,

1] In "Sheet1", please change B5 from : "Cell Phone - Check #1003" to "Utilities - Cell Phone - Check #1003"

Then,

2] In "Sheet2" D2 formula copied down :

=OFFSET(Sheet1!H$1,MATCH(A2&"*",Sheet1!$B$2:$B$9,0),INDEX({4,8,12,16,20,24,28},ROW(A1)))

 

[Excel Wizard]

Please try at D2

=INDEX(Table2,MATCH(A2&"*",Sheet1!$B$2:$B$9,),MATCH(LEFT(A2,3)&"*",Table2[#Headers],)+3)

 

[Yodelayheewho]

Bosco_Yip and Excel Wizard,
Both of your formulas worked perfectly on the example workbook. When I tried to use the formulas in the 'real' workbook, no luck and I think I may have figured out why. On the attached updated example.

Sheet 1 Column A = Checkbook register
I don't want the formula to reference this column because entries are not consistent, e.g., Gas could also be Speedway, Sunoco, etc.

Sheet 2 Column A = Budget Items
I created this column just as a 'long-hand' version of Column B.
Sheet 2 Column B = Balance Col (Balance Columns)
This column needs to be referenced in the formula. These terms are consistent. As mentioned before, I feel I need an Index Match to pair up the items on Sheet 2 Column B with headers on Sheet 1 Row 1. Once the match is true, then find the last entry in the matching column on Sheet 1.

I REALLY appreciate your help and level of expertise!

 

[p45cal]

In D2 of Sheet2:

=OFFSET(Table2[[#Headers],[CHECK REGISTER]],ROWS(Table2),MATCH([@[BALANCE COL]],Table2[#Headers],0)-1)

or non volatile and fulfilling the title of this thread:

=INDEX(Table2,ROWS(Table2),MATCH([@[BALANCE COL]],Table2[#Headers],0))

In your version of Excel, you may need to commit the formula to the sheet using Ctrl+Shift+Enter rather than just Enter then copy down, or you might need to select the whole of D2:E8 and enter the formula in all those cells at once using Ctrl+Shift+Enter. I can't test here.

 

[Peter Bartholomew]

You seem to have hard-wired column K into your formula. I have added an XLOOKUP formula and a MATCH/INDEX. I didn't bother to search for the last record because you are using a table and all the formulas run to the final record of the table.

= INDEX( Accounts,
    ROWS(Accounts),
    MATCH([@[BALANCE COL]], Accounts[#Headers], 0 )
  )

Looks familiar

 

[Yodelayheewho]

Hi Peter,
I can't see where your formula is on Sheet 2

 

[Yodelayheewho]

p45cal: In your formula, does the "8" represent offset 8 rows down? If so, then row "8" is the last row on the sample Sheet 1. My confidential worksheet keeps growing, I'm up to more than 3,400 rows and it grows weekly. How can we make the offset dynamic? Because I got the formula to finally work, but I had to change "8" to "3445", which is the last row of my data.

 

[p45cal]

p45cal: In your formula, does the "8" represent offset 8 rows down?

Yes, but I've altered it to be more general since, and added a second one.

 

[Peter Bartholomew]

Hi Peter,
I can't see where your formula is on Sheet 2

Sorry, I attached the download and not the saved file. The new one is attached now (fingers crossed)

 

[p45cal]

is to replace "Table2" in with "MASTERBUDGET".

yes:

=INDEX(MASTERBUDGET,ROWS(MASTERBUDGET),MATCH([@[BALANCE COL]],MASTERBUDGET[#Headers],0))

This formula should automatically become part of the table if it isn't already.

 

[Yodelayheewho]

Holy crumb! It worked p45cal! I made a test entry to expand the rows and it works!!!!! Thank you so much. Brilliant!!!

 

[Excel Wizard]

Please try

=LOOKUP(9^9,INDEX(Table2,,MATCH(B2,Table2[#Headers],)))

 

[Yodelayheewho]

This works too! Thank you! What does "9^9" represent?