# Arrays with INDEX formula

#### 2077delta

##### Board Regular
On Sheet 1 have two columns of data. In column A are card numbers and in column B are gallons. On sheet 2 I have a table of the card numbers (in no particular order) and whether they are for gas or diesel. I can use the combination INDEX and MATCH inside one formula to determine whether the cards on Sheet 1 are gas or diesel, but how do I embed this in an array formula to summarize the gallons on Sheet 1 by gas and diesel? Let's also assume that other factors prevent me from adding a column on Sheet 1 to indicate whether each card listed is gas or diesel. Here is the formula I'm currently using, but it returns a total for all the cards, not just for the ones identified as gas.

{=SUM((INDEX(cardnoindex,MATCH(TEXT(\$A\$8:\$A\$35,"#"),cardnoindexcard,0),2)="Gas")*(\$B\$8:\$B\$35))}

In this formula "cardnoindex" is the range name on Sheet 2 of the table of card numbers and type of fuel and "cardnoindexcard" is the range name of just the range of card numbers that make up "cardnoindex". You will also notice that I am converting the card numbers on Sheet 1 to text since they are entered as values on Sheet 1 and as text on Sheet 2. This is just one more of the idiosyncrasies of this file.

Any help would be greatly appreciated.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

##### MrExcel MVP
Would you specify the problem in terms of real ranges instead of named ranges?

Something like...

Sheet X, range A2:A100 houses card numbers...
Sheet X, range B2:B100 houses gallons...
Sheet Y, range ....

Needed: A sum of ...

#### 2077delta

##### Board Regular
Assume the following:
Sheet 1
Range A1:A25 = card numbers
Range B1:B25 = gallons
Sheet 2
Range A1:A8 = card numbers
Range B1:B8 = type of fuel (gas or diesel)

My formula would look like
{SUM((INDEX('Sheet2'!A1:B8,MATCH(TEXT(\$A\$1:\$A\$25,"#"),'Sheet2'!A1:A8,0),2)="Gas")*(\$B\$1:\$B\$25))}

Result would be the summary of gallons on Sheet 1 for "Gas".

Hope this helps

##### MrExcel MVP
Control+shift+enter, not just enter...
Code:
``````=SUM(
IF(ISNUMBER(MATCH(Sheet1!\$A\$1:\$A\$25,
IF(Sheet2!\$B\$1:\$B\$8="gas",Sheet2!\$A\$1:\$A\$8,""),0)),
Sheet1!\$B\$1:\$B\$25))``````

which yields a summary total for "gas".

If resolving the issue of text-numbers vs numbers must be done in the above formula...

Code:
``````=SUM(
IF(ISNUMBER(MATCH(Sheet1!\$A\$1:\$A\$25&"",
IF(Sheet2!\$B\$1:\$B\$8="gas",Sheet2!\$A\$1:\$A\$8,""),0)),
Sheet1!\$B\$1:\$B\$25))``````

Assume the following:
Sheet 1
Range A1:A25 = card numbers
Range B1:B25 = gallons
Sheet 2
Range A1:A8 = card numbers
Range B1:B8 = type of fuel (gas or diesel)

My formula would look like
{SUM((INDEX('Sheet2'!A1:B8,MATCH(TEXT(\$A\$1:\$A\$25,"#"),'Sheet2'!A1:A8,0),2)="Gas")*(\$B\$1:\$B\$25))}

Result would be the summary of gallons on Sheet 1 for "Gas".

Hope this helps

Last edited:

#### 2077delta

##### Board Regular
Thanks a ton. This works great, much simpler than what I was proposing. Array formulas are great, I just wish I had the same command of them as you and the other wizards out there. Is there any reference that gives a more detail description of the mechanics (and rules) on how these work? The help in Excel is pretty useless when it comes to this type on information.

##### MrExcel MVP
Thanks a ton. This works great, much simpler than what I was proposing. Array formulas are great, I just wish I had the same command of them as you and the other wizards out there. Is there any reference that gives a more detail description of the mechanics (and rules) on how these work? The help in Excel is pretty useless when it comes to this type on information.

You are welcome and thanks for the feedback.

The best we can do in order to grasp the mechanics of (such) formulas is, practically speaking, to study the formulas which solve clearly delineated problems...

The mechanics of the formula in question can be construed as...

The problem:

Total the gallon if a target card number is in the set of card numbers which are associated with the "gas" type of fuel.

Calculations needed:

(A) Determine the subset of card numbers that are gas card numbers:

==> IF(Sheet2!\$B\$1:\$B\$8="gas",Sheet2!\$A\$1:\$A\$8,"")

(B) Determine the target card numbers which are in A.

==> ISNUMBER(MATCH(Sheet1!\$A\$1:\$A\$25,A,0))

(C) Determine the gallon total for the target card numbers that are in A. That is, Total gallons if B=TRUE or SUM(IF(B,Gallons). Substitutions would then yield a runnable Excel formula.

Hope such a construal helps.

Replies
0
Views
319
Replies
3
Views
123
Replies
13
Views
526
Replies
0
Views
209
Replies
1
Views
185

### Forum statistics

1,190,957
Messages
5,983,837
Members
439,866
Latest member
jh3268 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back