need help, index/match function

**04twinmom** · 10-20-2011, 11:48 AM

Hello- I have a master tab in my spreadsheet set up as follows:

store name total shipped(feb) total shipped(march) etc for all months
store a
store b
store c
etc

I then have separate tabs with each month that have the store names in a list(if they ordered that particular month) and total shipped.

I am trying to use the Index and Match functions and not having luck. Here is the formula I have so far:
=INDEX('Feb ''11'!P12:P19,MATCH(1,($A12='Feb ''11'!A12:N123),0))

Can anyone tell me what is wrong with it? Also, if there is no store to reference, how do I have it return a 0 instead of #n/a??

Any help would be greatly appreciated!

**ConneXionLost** · 10-20-2011, 12:00 PM

The two dimensional lookup array (A12:N123) in the MATCH function is likely the culprit.

Cheers,

**04twinmom** · 10-20-2011, 12:04 PM

Sorry. that was a typo. it really should have been just in the A column for the match.

**ConneXionLost** · 10-20-2011, 12:11 PM

Place a double unary in front of the lookup array:

=INDEX('Feb ''11'!P12:P19,MATCH(1,--($A12='Feb ''11'!A12:A123),0))

and confrim with Ctrl-Shift-Enter.

Cheers,

**04twinmom** · 10-20-2011, 12:20 PM

Thank you that worked. What does the -- mean? Also, is there a way to eliminate an #n/a from coming back if no data is there? Could a sumproduct equation be used instead?

**ConneXionLost** · 10-20-2011, 12:22 PM

I also recommend you avoid using apostrophes (') in your tab names.

**ConneXionLost** · 10-20-2011, 12:32 PM

Your MATCH function can return a row number up to 111, but your INDEX function array has only 8 rows. Is this another typo?

To avoid the NA#, try using an IF & COUNTIF combination like this:

=IF(COUNTIF('Feb ''11'!A12:A123,$A12),INDEX('Feb ''11'!P12:P19,MATCH(1,--($A12='Feb ''11'!A12:A123),0)),0)

Cheers,

**ConneXionLost** · 10-20-2011, 12:39 PM

The Double Unary (--) are a method of coercion, which is a way to convert a Boolean value (TRUE or FALSE) to its numeric equivalent (1 or 0). There are several ways to accomplish it, (adding a zero, or multiplying by 1 are examples). One of the, if not the, fastest and most efficient ways is to use two negation operators together, which changes the sign twice, resulting in a numeric value.

**04twinmom** · 10-20-2011, 01:09 PM

Thanks again! That worked. Now, instead of returning n/a. I get a blank and can't subtotal. is there an ifblank statement I can add to my sum equation? Oh and the column ranges were incorrect. sorry about all of my typos!

**ConneXionLost** · 10-20-2011, 01:14 PM

So, can you show the formula you now have (without the typos)?

**04twinmom** · 10-20-2011, 01:20 PM

Here it is:

=IF(COUNTIF('Feb 11'!$A$12:$A$200,$A11),INDEX('Feb 11'!$P$12:$P$200,MATCH(1,--($A11='Feb 11'!$A$12:$A$200),0)),"")

Also, I should say when I am trying to total, it is every other cell ie b11+e11+611 etc.. and not a range.