Large Data - Index Match Match with another function?

**d7882** · 10-11-2017, 04:41 PM

Hello,

I'm looking for some help with a large set of data that I can pull certain numbers from to create an interactive graph. I'm familiar with the IndexMatchMatch function but the issue that I have is that the data has the same cell name in each particular "store", so I can't wrap my head around how I can specify a store and the right data.

For example, Lets say I want to pull Rental Income from Store 2, not store 1,3,4, etc. for Feb 2014. I've attached a small sample of the data with random numbers. Hope you guys could help me out with this project. The alternative is to make an indexmatchmatch for each store, but I'm hoping there's another solution.

Thanks in advance.

**Lanceh** · 10-11-2017, 04:51 PM

Hate to ask stupid questions but 1) is all your data in this format and 2) what is the max store number in your data?

**d7882** · 10-12-2017, 08:24 AM

1) The data doesn't have to fit this format - what do you recommend?
2) The max number of stores changes as we acquire and sell. If a max helps with a formula, then let's say 200.

**Glenn Kennedy** · 10-12-2017, 08:30 AM

One possibility... out of many.

**d7882** · 10-12-2017, 08:32 AM

I would like the worksheet to hold all the stores - that way I don't have to go through several sheets.

**Glenn Kennedy** · 10-12-2017, 08:52 AM

see post 4 above..

**d7882** · 10-12-2017, 08:56 AM

Thanks Glenn - I think I can work with that.

I marked it as Solved, but I have one additional question. I don't see where it states which date I want the numbers pulled form. So if I wanted Feb 2014 or if I wanted June 2015, etc.

**Glenn Kennedy** · 10-12-2017, 09:07 AM

Oh pooh. I missed that bit. leave it with me for a moment.

**Glenn Kennedy** · 10-12-2017, 09:24 AM

Try this...

**d7882** · 10-12-2017, 09:28 AM

Great stuff - I'll have to study that formula.

Thanks again!

**Glenn Kennedy** · 10-12-2017, 09:32 AM

You're welcome.

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

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

**Lanceh** · 10-12-2017, 09:39 AM

Originally Posted by d7882

1) The data doesn't have to fit this format - what do you recommend?
2) The max number of stores changes as we acquire and sell. If a max helps with a formula, then let's say 200.

Looks like Glenn beat me to the solve and did so in a cleaner way. Cheers!

**d7882** · 10-12-2017, 10:24 AM

Glenn - if you have time, do you think you could go through the formula for others to read and learn?

Personally, I'm not too familiar with why we're +Match, then -Match

Then there's the -Match(True,index(isnumber###) functions that's really throwing me off.

**Glenn Kennedy** · 10-12-2017, 11:00 AM

Off the bumpy bus... at a stable table... and a simpler formula.

=INDEX(Sheet1!$C:$Z,MATCH(A2,Sheet1!$B$2:$B$80,0)+MATCH(B2,Sheet1!$B$2:$B$80,0),MATCH(C2,Sheet1!$C$2:$Z$2,0))

Red: Locate the row with the store (returns 1 in the sheet)

Orange Locate the row with the category (returns 2 in the sheet). Since the rows are always int he same order, this efectively acts like OFFSET, adding on the correct number of rows to return the correct Excel sheet row

Cyan: locates the correct date column

Blue: Now you have the correct row and column... The result is returned from the 2D array

**d7882** · 10-12-2017, 03:41 PM

That's a cleaner look - thanks

**Glenn Kennedy** · 10-12-2017, 03:51 PM

The previous one was written on a bus journey down to Dublin, laptop on my knee.

Glad it's sorted. So....

You're welcome.

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

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Large Data - Index Match Match with another function?

LinkBack

Thread Tools

Rate This Thread

Display

Large Data - Index Match Match with another function?

Re: Large Data - Index Match Match with another function?

Re: Large Data - Index Match Match with another function?

Re: Large Data - Index Match Match with another function?

Re: Large Data - Index Match Match with another function?

Re: Large Data - Index Match Match with another function?

Re: Large Data - Index Match Match with another function?

Re: Large Data - Index Match Match with another function?

Re: Large Data - Index Match Match with another function?

Re: Large Data - Index Match Match with another function?

Re: Large Data - Index Match Match with another function?

Re: Large Data - Index Match Match with another function?

Re: Large Data - Index Match Match with another function?

Re: Large Data - Index Match Match with another function?

Re: Large Data - Index Match Match with another function?

Re: Large Data - Index Match Match with another function?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Match Index Function for Large Array

[SOLVED] Accounting for duplicates with LARGE function (within index match)

[SOLVED] INDEX/MATCH Array within LARGE Function?

Problems applying INDEX-MATCH-MATCH function on other data

How to: Return next value w/ same # in index match large function?

Index match with large function

Using Index & Match with the Large function

Bookmarks

Bookmarks

Posting Permissions