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="width: 215"]
<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="width: 192"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]apples
[/TD]
[TD="class: xl65, bgcolor: transparent"]x
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]oranges
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]bananas
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: 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.

 

[AhoyNC]

Like this?
If you set up your cell references properly you can just enter the formula in the first cell and copy over and down.

This is an array formula and must be enter with CTRL-SHIFT-ENTER

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	fruit	quanity	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
11
Sheet1

 

[Rick Rothstein]

Please do not post your question more than one time... it is not very fair to the volunteers here when you do. For example, by the time AhoyNC posted his solution here, you had already indicated you had an acceptable answer in your other (duplicate) thread. Result... you ended up having AhoyNC waste his time developing an answer you no longer needed because he did not know you had posted your question twice. Not only is that not fair to AhoyNC, but is is also unfair to others seeking answers here (like you are doing) who AhoyNC could have devoted his time to instead. If you do not see the unfairness in that, assume someone else did what you did (posted your question twice) and it was your question AhoyNC did not devote his time to because that other person had him wasting his time developing a solution to a question that no longer needed an answer.