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.
{=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.