2-Dimensional Vlookup

[bbehr7]

Hey!

I was able to figure out how to do a two-dimensional vlookup with the match function. But I also need to do a 2 dimension vlookup in the reverse order. (ex. bring the info from a list into a chart.) I've included a very simple example below to help.


Below is the information I am given: Both the fruit and the quanity columns are entered, but there are multiple sources of each, but only one combination of each. Ex. apple 1, apple 2, bannana 2
[TABLE="class: cms_table"]
<TBODY>[TR]
[TD]
fruit</SPAN>[/TD]
[TD]quanity</SPAN>[/TD]
[TD]price(each)</SPAN>[/TD]
[/TR]
[TR]
[TD]apples </SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"]1.2</SPAN>[/TD]
[/TR]
[TR]
[TD]apples </SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[/TR]
[TR]
[TD]apples </SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[TD="align: right"]0.9</SPAN>[/TD]
[/TR]
[TR]
[TD]bananas</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"]0.95</SPAN>[/TD]
[/TR]
[TR]
[TD]bananas</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD="align: right"]0.9</SPAN>[/TD]
[/TR]
[TR]
[TD]bananas</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[TD="align: right"]0.85</SPAN>[/TD]
[/TR]
[TR]
[TD]oranges</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"]1.25</SPAN>[/TD]
[/TR]
[TR]
[TD]oranges</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD="align: right"]1.15</SPAN>[/TD]
[/TR]
[TR]
[TD]oranges</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[TD="align: right"]1.05</SPAN>[/TD]
[/TR]
</TBODY>[/TABLE]




I need the information to populate into a chart like the one below:

[TABLE="class: cms_table"]
<TBODY>[TR]
[TD="class: cms_table_xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: cms_table_xl65, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: cms_table_xl65, width: 64, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent"]apples[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent"]x[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent"]oranges[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl65, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent"]bananas [/TD]
[TD="class: cms_table_xl65, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl65, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl65, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]



So where I entered an "X" in the correspond Apple & 1 box should be 1.2 and the same for the rest of the information.

Please help! I have been trying to figure this out for a week and cannot come up with a solution.

 

[Andrew Poulsom]

Example (formula in F2 copied down and across):

Excel 2010
	A	B	C	D	E	F	G	H
1	fruit	quantity	price(each)			1	2	3
2	apples	1	1.2		apples	1.2	1	0.9
3	apples	2	1		oranges	1.25	1.15	1.05
4	apples	3	0.9		bananas	0.95	0.9	0.85
5	bananas	1	0.95
6	bananas	2	0.9
7	bananas	3	0.85
8	oranges	1	1.25
9	oranges	2	1.15
10	oranges	3	1.05
Sheet5

Cell Formulas
Range	Formula
F2	=INDEX($C$2:$C$10,MATCH(1,INDEX(($A$2:$A$10=$E2)*($B$2:$B$10=F$1),),FALSE))

 

[bbehr7]

I am getting an N/A error. I am only using excel 2007 could that be the reason?

 

[bbehr7]

I figured out the cause of the error!

Thanks so much for your help!

 

[bbehr7]

Thanks so much, you are awesome!