Check several values from different columns in Excel

Question

I have a data table that has the car brand in column A and the vehicle type (SUV, Sedan, etc.) in column B. I would like to automatically check in another sheet whether, for example, the make VW in my table includes an SUV.

Is there a possibility to solve it without any macros?

Example for data table (the real table has nearly 300 entrys):

Brand	Type
VW	SUV
Ford	SUV
Audi	Sedan
Ford	Cabrio
VW	Sedan
Tesla	Sedan

Example for lookup-sheet

Brand	SUV	Sedan	Cabrio
VW	x	x
Ford	x		x
Audi		x
Tesla		x

Answer 1

If you use Office 365, here is another way to achieve this. You can add column A and column D as follows. Then type formulas in G1 and F2 using the UNIQUE function. Finally, type the formula in G2 as follows and copy-and-paste. Done.

Answer 2

You can achieve this goal a few ways. One I lean towards is using a PivotTable since this allows you to scale the table with more car brands and types without manually adding them and adjusting formulas.

To do this, simply highlight the first table and under Insert, chose PivotTable and drag the type in the top right square and the brand in the bottom left. In the values you can pull any field from the original table to get a Count, if you don't have a field make one and put anything in there (1, x, A, etc.) just something to get a count (or sum if you use 1's). This won't give you x's like you have here but it will show in binary the same info.

To achieve this using a formula you will need to use an Index/Match combined within an IF statement checking for errors. I'm assuming you're trying to add x to the table to show VW has an SUV. So this formula would go into the values of that table, referring to the first. We'll call the first table _Cars and the second I'll assume starts at A1.

To do this in a formula have the table similar to how you have the second one here. Then in the values put the following formula.

=IF( ISERROR( INDEX(_Cars, MATCH(1,(_Cars[Brand]=$A2)*(B$1=_Cars[Type]),0),2)),"","x")

This will fill your table with x's where there's a value to pull for the Brand & Type combination. Make sure if you go this route you hit CTRL+SHIFT+ENTER

Answer 3

Your hint helped me a lot to resolve the problem! Thank you. I've got it done!