INDEX & MATCH Formula

[oboegal21]

Hello,

I am new to the INDEX & MATCH formula used instead of VLOOKUP and just have a question...

I would like to know how I would reference a table rather than a worksheet in the formula?

For example the formula referencing a different worksheet appears as:

=INDEX(Sheet2!H:H,MATCH('Sheet1'!$A10,Sheet2!$E:$E,0))

I want the 'sheet' references to actually be a defined table. I am having trouble getting the formula to work when I do this.

Any suggestions?

Thank you!

[mcmahobt]

What exactly are you having trouble with? What is the formula currently returning?

[oboegal21]

Well I think I'm simply doing it wrong by incorporating a Table rather than a worksheet because it is just giving me an incorrect formula error.

How would this same formula look by referncing
Table 1
Table 2
(assuming these are the defined table names)

?

[Glenn Kennedy]

With Index Match, you reference COLUMNS in the Table. Take a look at this small example. If this doesn't help - can you post a sample of your own Excel sheet?

[mcmahobt]

For Excel Tables, an Index/Match formula would look something like this:

=INDEX(Table1[ColumnHeader1],MATCH(Sheet1!$A10,Table1[ColumnHeader2],0))

Without seeing how your data is setup, it is hard to give you more help beyond that.

[oboegal21]

Ok, I have attached an example of my spreadsheet.

To explain this a bit -

Tab 1 "Budget vs Analysis" is a template I created to better illustrate the data in Tab 2 "BvA'.

Tab 2 "BvA" is a downloaded report from a finance system that comes out exactly as it is in this worksheet.

My goal is to have Tab 1 be a functioning template that I can just plop the downloaded report into the worksheet, set a table name to the worksheet, and have the formulas in the template to the rest.

The report is much larger than just this set of data so it would be great to have a formula to pull in the data from the downloaded report automatically. This report is pulled on a monthly basis so the data entry and clean-up gets tedious.

Thank you for your help!

[mcmahobt]

I'm not sure if I'm missing something, but aren't the formulas already pulling the correct data in the Budget vs. Actual worksheet?

[oboegal21]

Yes - they are doing just that but they are referring to the specific worksheet.

I want it to pull from a table because when I have to re-run this report the worksheet will then be different. If I can just assign it a table name then I don't have to worry about the worksheet changing each month.

Does that make sense?

[Glenn Kennedy]

The problem is that your Table isn't a Table... at least it isn't a structured Table, where you can use "smarter" references. Though, I should add plenty folk don't like them. The stuff in columns A to C is not arranged in a way that makes Table use easy.

[oboegal21]

A-C in which worksheet?

BvA is unfortunately a mess of a worksheet. The way it is here is exactly how it is downloaded from the financial system... definitely not set up in any way how I would set it up! Which is why I created the first tab - as an effort to have a cleaner template pull from the original data worksheet without having to do too much extra work.

Do you have any suggestions for how I could achieve what I am looking to achieve? Even if it is use of a different formula? I think we were on track by the formula you suggested of references a "table" and having it pull from a column header. Would something like that work with these two worksheets?

[Glenn Kennedy]

Hang on... you're overcomplicating htis (and so am I). Why can't you delete everthing in BVA each update and just paste the new stuff in. Providing the headings don't change, your Table will update. In reality, you don't need iNDEX-MATCH, as you're just using it to rearrange data. See a simpler alternative in Sheet1 - that you can pretty up. Alternatively, use your existing format in your own report format. It's not doing any harm!!

[oboegal21]

Yes, perhaps you are correct that this has been over-complicated!

Headings all stay the same - a row may be added or deleted but that should be easy to spot when the template pulls data (I have 'check totals' in place as well if something is not in balance in the template).

I will continue using my cleaned-up template as I have some conditional formatting things embedded in there but if I keep the worksheet BvA and just do a straight copy-paste of the new data each month into BvA (and effectively using this as another template to house the downloaded data) then theoretically I could also leave the INDEX-MATCH formula to pull in the updated data in BvA to my cleaned-up template.

Oof - that was a lot of words to say I think we found our simplified solution!

[Glenn Kennedy]

A-C in BVA - it is a bit of a mess!! I'll come back to you in a minute or 10...

[mcmahobt]

Glenn may come back with some Excel wizardry, but this may be better suited for VBA if your data ranges will be inconsistent.

[oboegal21]

The data ranges from the downloaded file *should* be the same - with exception to potentially some added or subtracted rows - but that is easy to work around. The number of columns or order of the columns in the downloaded file should not change though.

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

I'm not sure if we really did help much on this occasion, but if you think that we did, it'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.