Matching values between linear sheet and 2 dimensional sheet

[booms]

I’m finding this very hard to get working the way I’d like to, I’ve been trying to use a combination of ADDRESS, SUBSTITUTE, MATCH and it’s all getting a bit unwieldy.


I have two data sources that I want to compare to make sure the values are the same.
One has all numerical values in a single column with a set conditions in the preceding columns. Nice and straight forward and easy to use a SUMIFS on.
The other data source has the values in a two dimensional table, with one set of conditions on the X-axis and other other on the Y-axis.
How can I pull in the values from the 2nd sheet to the first so I can check them off against each other?
e.g.


[TABLE="width: 182"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Car Model[/TD]
[TD]Car Type[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]VW[/TD]
[TD]Hatchback[/TD]
[TD="align: right"]13413[/TD]
[/TR]
[TR]
[TD]VW[/TD]
[TD]Midsize[/TD]
[TD="align: right"]13534[/TD]
[/TR]
[TR]
[TD]VW[/TD]
[TD]Luxury[/TD]
[TD="align: right"]24564[/TD]
[/TR]
[TR]
[TD]VW[/TD]
[TD]MPV[/TD]
[TD="align: right"]23453[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]Hatchback[/TD]
[TD="align: right"]67457[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]Midsize[/TD]
[TD="align: right"]42344[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]Luxury[/TD]
[TD="align: right"]24356[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]MPV[/TD]
[TD="align: right"]24564[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 283"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Sheet2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]VW[/TD]
[TD]Audi[/TD]
[/TR]
[TR]
[TD]Hatchback[/TD]
[TD="align: right"]13413[/TD]
[TD="align: right"]67457[/TD]
[/TR]
[TR]
[TD]Midsize[/TD]
[TD="align: right"]13534[/TD]
[TD="align: right"]42344[/TD]
[/TR]
[TR]
[TD]Luxury[/TD]
[TD="align: right"]24564[/TD]
[TD="align: right"]24356[/TD]
[/TR]
[TR]
[TD]MPV[/TD]
[TD="align: right"]23453[/TD]
[TD="align: right"]24564[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Here's one way.

Let's assume all your data starts in cell A1 on the relevant sheet.
So sheet 1, cell A1 contains "Car Model".
Sheet 2, cell A1 is blank, and A2 contains "Hatchback".

=OFFSET(Sheet2!$A$1,MATCH(Sheet1!B2,Sheet2!A$2:A$5,0),MATCH(Sheet1!A2,Sheet2!B$1:C$1,0),1,1)

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D
1	Car Model	Car Type	Price
2	VW	Hatchback	13413	13413
3	VW	Midsize	13534	13534
4	VW	Luxury	24564	24564
5	VW	MPV	23453	23453
6	Audi	Hatchback	67457	67457
7	Audi	Midsize	42344	42344
8	Audi	Luxury	24356	24356
9	Audi	MPV	24564	24564
Sheet1

Cell Formulas
Range	Formula
D2	=INDEX(sheet2!$B$2:$C$5,MATCH(B2,sheet2!$A$2:$A$5,0),MATCH(A2,sheet2!$B$1:$C$1,0))

 

[booms]

How about

Excel 2013/2016

	A	B	C	D
Car Model	Car Type	Price
VW	Hatchback
VW	Midsize
VW	Luxury
VW	MPV
Audi	Hatchback
Audi	Midsize
Audi	Luxury
Audi	MPV

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=INDEX(sheet2!$B$2:$C$5,MATCH(B2,sheet2!$A$2:$A$5,0),MATCH(A2,sheet2!$B$1:$C$1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks very much to both of you - using MATCH in 2D was exactly what I needed.

 

[Fluff]

You're welcome & thanks for the feedback