Formula to Return Cell Value based on Row and Column

[Hobolord]

Hello!

I am using excel 2010 on Windows 7.

I am trying to figure out a formula to return the value of a cell that is in the same column as criteria x and the same row as criteria y. For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Ford[/TD]
[TD]Dodge[/TD]
[TD]Chevy[/TD]
[/TR]
[TR]
[TD]2009[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]2010[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2011[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

I would want my formula to be able to pick the count of 2010 Dodges (7).

The formula that I have now is:

=VLOOKUP($C$6,'HFM DATA'!A3:N500,MATCH($C$9,'HFM DATA'!A3:N500,0),FALSE)

Where C6 is the first criteria and C9 is the second criteria. But it is returning a #NA, and there definitely is a number there.

Does anyone know what I'm doing wrong, or even have a better solution?

Thanks for your time!

 

[Caribeiro77]

Can you please tell us what are the criterias? Are they the year in C6 and brand in C9? If so you can use the INDEX with the MATCH like so:

	A	B	C	D
Criteria
Criteria
Dodge
Ford	Dodge	Chevy
Result:

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B17[/TH]
[TD="align: left"]=INDEX($B$12:$D$14,MATCH(C6,$A$12:$A$14,0),MATCH(C9,$B$11:$D$11,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Hobolord]

I have C6 as the brand and C9 as the year. Which seems wrong saying it now, but I tried changing them and had no luck that way either. Thanks for the question!

 

[Caribeiro77]

Have you tried my formula?

 

[Hobolord]

Yes, sorry I just tried it.

I am still getting an #NA. Below is how I translated it to the full data from the example:

=INDEX('HFM DATA'!$B$5:$N$500,MATCH($C$6,'HFM DATA'!$A$3:$A$500,0),MATCH($C$9,'HFM DATA'!$D$3:$N$3,0))

Any ideas?

 

[Caribeiro77]

Your criterias are "inside" the INDEX array?
The criterias normally are outside the array's of the formulas?
Can you post a small example of your sheet and how's your data is set it up?

 

[Hobolord]

Sure! The below is the range A3:F15

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="width: 111, align: right"]811[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="width: 92, align: right"]819[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="width: 92, align: right"]822[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70, align: right"]6120001[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]Shrinkage[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl183, width: 111, align: right"]8,529,173[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl183, width: 92, align: right"]15,945,578[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl183, width: 92, align: right"]7,088,880[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70, align: right"]6120002[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]Reserve - Obsolete[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70, align: right"]6120003[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]Reserve - Excess Inventory[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70, align: right"]6120004[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]Reserve - Changes in Standard[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70, align: right"]61300[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]Other Inventory[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl183, width: 111, align: right"]-132,149[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl183, width: 92, align: right"]-1,566,326[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl183, width: 92, align: right"]-43,076[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70, align: right"]6165001[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"] Total Inventory Reserves[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl183, width: 111, align: right"]2,335,083[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl183, width: 92, align: right"]4,104,347[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl183, width: 92, align: right"]2,055,434[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70, align: right"]6165002[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]Contract Loss Provisions[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Caribeiro77]

Where are the criterias in your example?

 

[Hobolord]

They are in cells C6 and C9 on a different Tab named 'Summary'.

C6 is the number in row 3 and C9 is the number in column A.

 

[Caribeiro77]

both criterias are numbers?
you may have to change the MATCH TYPE to 1 like bellow:

=INDEX('HFM DATA'!$B$5:$N$500,MATCH($C$6,'HFM DATA'!$A$3:$A$500,1),MATCH($C$9,'HFM DATA'!$D$3:$N$3,1))