INDEX Forumula

[Ruzzo]

Sheet 1 has the following:
A B C D
1 Trucks (20.00) 10.00 10.00
2 Cars (400.00) 20.00 20.00



Sheet 2 has Trucks and Cars listed again as such- how do I INDEX Sheet 2, to input the same info in cells C1 & D1…and C2 & D2, as it has in Sheet 1.

In other words, Sheet 2 may have different values in the ‘B’ column. And the order of Trucks, Cars, and other vehicles may be not in the same order.

Hope this makes sense.

Thanks in advance
Ruzzo

 

[Ruzzo]

To go further with this- I now have data in column E in Sheet 1, with different text variables.

Now, is there a way to find anything with Cars and the text JT in column E, from Sheet 1. I would want to sum all of these. Some may have JT, some may not. But I just want the JT's to return, summed.
thanks in advance.
Ruzzo

 

[Aladin Akyurek]

To go further with this- I now have data in column E in Sheet 1, with different text variables.

Now, is there a way to find anything with Cars and the text JT in column E, from Sheet 1. I would want to sum all of these. Some may have JT, some may not. But I just want the JT's to return, summed.
thanks in advance.
Ruzzo

Control+shift+enter, not just enter:

=INDEX('Sheet 1'!$C$2:$C$,MATCH(1,IF('Sheet 1'!$A$2:$A$4="Cars",IF('Sheet 1'!$E$2:$E$4="JT",1)),0))

This formula effects as it were a multiconditional retrieval.

 

[Ruzzo]

Control+shift+enter, not just enter:

=INDEX('Sheet 1'!$C$2:$C$,MATCH(1,IF('Sheet 1'!$A$2:$A$4="Cars",IF('Sheet 1'!$E$2:$E$4="JT",1)),0))

This formula effects as it were a multiconditional retrieval.

Thanks, I'm almost there, but this didn't quite work.
First of all, when do I enter the Control+shift+Enter?

Secondly, I want to post my example:

Sheet 1 will have, in order as listed, per cell:
A1:A6 – Cars, Trucks, Cars, Boats, Cars, Boats
C1:C6 – 10, 8, 5, 7, 11, 2
E1:E6 – JT,ER,ER,AV, JT, JT

Sheet 2
A:A20 will have a series of Cars, Trucks, Boats, etc…per cell

I want to match what is in column A in sheet 2 to what is in column A of sheet 1, and return a SUM, if it has JT in column E of Sheet 1.

The result of my example should return Cars in Sheet 2, with a value of 21. It sums C1 and C5, because it matches to both what is in Column E (JT), and matches in column A.
It leaves out the value of 5, because even though it matches ‘Cars’ in this example, it does not contain JT in column E.

Also, for Boats, I should return a value of 2.


Thanks again.
Ruzzo

 

[Ruzzo]

Control+shift+enter, not just enter:

=INDEX('Sheet 1'!$C$2:$C$,MATCH(1,IF('Sheet 1'!$A$2:$A$4="Cars",IF('Sheet 1'!$E$2:$E$4="JT",1)),0))

This formula effects as it were a multiconditional retrieval.

Thanks, I'm almost there, but this didn't quite work.
First of all, when do I enter the Control+shift+Enter?

Secondly, I want to post my example:

Sheet 1 will have, in order as listed, per cell:
A1:A6 – Cars, Trucks, Cars, Boats, Cars, Boats
C1:C6 – 10, 8, 5, 7, 11, 2
E1:E6 – JT,ER,ER,AV, JT, JT

Sheet 2
A:A20 will have a series of Cars, Trucks, Boats, etc…per cell

I want to match what is in column A in sheet 2 to what is in column A of sheet 1, and return a SUM, if it has JT in column E of Sheet 1.

The result of my example should return Cars in Sheet 2, with a value of 21. It sums C1 and C5, because it matches to both what is in Column E (JT), and matches in column A.
It leaves out the value of 5, because even though it matches ‘Cars’ in this example, it does not contain JT in column E.

Also, for Boats, I should return a value of 2.


Thanks again.
Ruzzo

 

[Aladin Akyurek]

Thanks, I'm almost there, but this didn't quite work.
First of all, when do I enter the Control+shift+Enter?

Secondly, I want to post my example:

Sheet 1 will have, in order as listed, per cell:
A1:A6 – Cars, Trucks, Cars, Boats, Cars, Boats
C1:C6 – 10, 8, 5, 7, 11, 2
E1:E6 – JT,ER,ER,AV, JT, JT

Sheet 2
A:A20 will have a series of Cars, Trucks, Boats, etc…per cell

I want to match what is in column A in sheet 2 to what is in column A of sheet 1, and return a SUM, if it has JT in column E of Sheet 1.

The result of my example should return Cars in Sheet 2, with a value of 21. It sums C1 and C5, because it matches to both what is in Column E (JT), and matches in column A.
It leaves out the value of 5, because even though it matches ‘Cars’ in this example, it does not contain JT in column E.

Also, for Boats, I should return a value of 2.


Thanks again.
Ruzzo

2007 and beyond...

=SUMIFS('Sheet 1'!$C$1:$C$5,'Sheet 1'!$A$1:$A$5,A1,'Sheet 1'!$E$1:$E$5,"JT")

Otherwise:

=SUMPRODUCT('Sheet 1'!$C$1:$C$5,--('Sheet 1'!$A$1:$A$5=A1),--('Sheet 1'!$E$1:$E$5="JT"))

 

[Ruzzo]

2007 and beyond...

=SUMIFS('Sheet 1'!$C$1:$C$5,'Sheet 1'!$A$1:$A$5,A1,'Sheet 1'!$E$1:$E$5,"JT")

Otherwise:

=SUMPRODUCT('Sheet 1'!$C$1:$C$5,--('Sheet 1'!$A$1:$A$5=A1),--('Sheet 1'!$E$1:$E$5="JT"))

Thanks
I am on Excel 2010

I tried the formula, but came back with a #VALUE!
Does the sheet name have to have the apostrophe's around the name? I linked the pages, but the apostrophe's didn't appear...when I manually typed them, and got out of the cell, it didn't appear as well.

 

[Ruzzo]

Sorry for all of my replies.

Once again, I used the top formula since I am using Excel 2010.
I am getting closer. I deleted the last part of the query (the one referencing the JT), and it worked...but it brought over everything.
I do need that reference to include JT, only - Can you confirm the last part is correct?
Thanks again.

 

[Aladin Akyurek]

Sorry for all of my replies.

Once again, I used the top formula since I am using Excel 2010.
I am getting closer. I deleted the last part of the query (the one referencing the JT), and it worked...but it brought over everything.
I do need that reference to include JT, only - Can you confirm the last part is correct?
Thanks again.

JT must be placed between a pair of double quotes, not single quotes or apostrophes...

 

[Ruzzo]

JT must be placed between a pair of double quotes, not single quotes or apostrophes...

Double quotes are being used, but it doesn't work.

 

[Aladin Akyurek]

Double quotes are being used, but it doesn't work.

Sheet 1

Cars		10		JT
Trucks		8		ER
Cars		5		ER
Boats		7		AV
Cars		11		JT
Boats		2		JT

<colgroup><col style="width: 48pt;" span="5" width="64"> <tbody>
</tbody>

Sheet2

Cars	21
Trucks	0
Boats	2

<colgroup><col style="width: 48pt;" span="2" width="64"> <tbody>
</tbody>

B2, just enter and copy down:

=SUMIFS('Sheet 1'!$C$1:$C$6,'Sheet 1'!$A$1:$A$6,A1,'Sheet 1'!$E$1:$E$6,"JT")

Check for stray spaces or non-printable char around the entries in 'Sheet 1'!$E$1:$E$6.