# Index and Match help needed

1. ## Index and Match help needed

Hi,
I have a spreadsheet with budget information. I want to use that sheet to populate a different one that will combine all the codes for all the locations into one line. I assume I need Index and Match for this.
For example, I may have loc 1 with 6410.1000 for \$50.00 and loc 2 with 6410.1000 for \$100.00 and so on the new sheet I want 6410.1000 to show \$150.00 total.
There will be many entries to this.
I attached the spreadsheet with the data. The blue cells show where there are multiple account codes for multiple locations which are what I want to combine into one line on a new sheet.
Thank so much...don't know what I would do without this site.

2. ## Re: Index and Match help needed

Wouldn't this just be a =SUMIF issue?

https://support.office.com/en-us/art...2-1697a653039b

3. ## Re: Index and Match help needed

Sorry, I was thinking this through wrong.
I don't want the numbers to combine...that will happen on the pivot table.
I want to want to do a lookup on that test budget sheet, and have it return the value I need.
So on my new sheet, I want to look up company 01 AND code 6410.1000 and return the dollar amount shown for that row.
Then look up company 02 AND code 6410.1000 and return the dollar amount shown for that row.
Does that make sense.

Too much going through my mind and in a crunch to make this work.

4. ## Re: Index and Match help needed

No problem. Where do you want the results?

5. ## Re: Index and Match help needed

Thank you.
I will attach the form where the information should populate. It would go in the Jan 2015 and Feb 2015, etc. columns.

6. ## Re: Index and Match help needed

Can you provide a couple examples of your desired outcomes? For example, on the worksheet "Entry Sheet" the January 2015 total for Co 01 and Account 6124.0000-14-10 should be \$XXX (should appear in Cell F2). And explain where that data has come from. For example, \$XXX is the subtotal for that company and that account in January listed in Column E on Sheet1.

7. ## Re: Index and Match help needed

Here is an updated sheet.
So the Entry sheet will come from the Database tab. Right now it looks like a simple copy and paste could be done, but it will be more complicated than that. So basically, the so co 01 and acct 6623.6000-14-45 should look up on database tab and return what it finds for co 01 and 6623.6000-14-45 and the result should be 2664.00.

8. ## Re: Index and Match help needed

Will a company/account combination ever have more than one row?

9. ## Re: Index and Match help needed

It should not. They should only ever have one account for each company as it is already compiled into one on the database tab.

10. ## Re: Index and Match help needed

Then I'm not really sure that you need to do anything. Take the Database spreadsheet and just insert Budget columns like you have in the Entry Sheet. It doesn't seem that you're performing any calculations. Just reformatting the data you already have.

11. ## Re: Index and Match help needed

The data that is on the entry sheet in columns A:E will change, I pasted that in there for now. As the managers key in those codes that they want to use for each company, I want it to populate in the Jan 2015, Feb, 15 column, etc. The database tab will have thousands of rows of data when it is all done. So when the entry sheet has those codes added to it, the months should populate by looking them up on the database tab. I only filled the entry sheet with data so you could see what the end result was as I thought that was what you were asking.

12. ## Re: Index and Match help needed

Maybe this is what you're looking for. Populated the data for January. If it's correct, I'd just copy the formulas over and THEN insert columns for the budget lookups.

13. ## Re: Index and Match help needed

OMG.....YES!!!!!!
That is exactly what I needed hoyasaxa215!
Thank you SO much!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1