# Matching values between linear sheet and 2 dimensional sheet

#### booms

##### Board Regular
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.

 Sheet 1 Car Model Car Type Price VW Hatchback 13413 VW Midsize 13534 VW Luxury 24564 VW MPV 23453 Audi Hatchback 67457 Audi Midsize 42344 Audi Luxury 24356 Audi MPV 24564

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 Sheet2 VW Audi Hatchback 13413 67457 Midsize 13534 42344 Luxury 24564 24356 MPV 23453 24564

<colgroup><col><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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)

Excel 2013/2016
ABCD
1Car ModelCar TypePrice
2VWHatchback1341313413
3VWMidsize1353413534
4VWLuxury2456424564
5VWMPV2345323453
6AudiHatchback6745767457
7AudiMidsize4234442344
8AudiLuxury2435624356
9AudiMPV2456424564
Sheet1
Cell Formulas
RangeFormula
D2=INDEX(sheet2!\$B\$2:\$C\$5,MATCH(B2,sheet2!\$A\$2:\$A\$5,0),MATCH(A2,sheet2!\$B\$1:\$C\$1,0))

Excel 2013/2016
ABCD
1Car ModelCar TypePrice
2VWHatchback1341313413
3VWMidsize1353413534
4VWLuxury2456424564
5VWMPV2345323453
6AudiHatchback6745767457
7AudiMidsize4234442344
8AudiLuxury2435624356
9AudiMPV2456424564

</tbody>
Sheet1

Worksheet Formulas
CellFormula
D2=INDEX(sheet2!\$B\$2:\$C\$5,MATCH(B2,sheet2!\$A\$2:\$A\$5,0),MATCH(A2,sheet2!\$B\$1:\$C\$1,0))

</tbody>

<tbody>
</tbody>

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

You're welcome & thanks for the feedback

Replies
1
Views
858
Replies
4
Views
420
Replies
1
Views
429

1,203,075
Messages
6,053,393
Members
444,661
Latest member
liamoohay

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back