how to do matching 2 criterias

[Guest]

Dear Sir,

I need help to match 2 column ranges, how is that possible with excel
worksheet.
I tried with INDEX and MATCH but only can do with 1 range

example:
column 1 brand of vehicles (chevrolet, toyota, renault. etc)
column 2 type of car (1000cc, 1300cc, 2000cc, etc)
then say I need to match the price of these vehicles in other worksheet that
the list is not in the same order

Best Regards,
Harutoni

 

[Guest]

One way ..

Assuming source data in Sheet1, cols A to E say, where col A = vehicle brand
(eg toyota), col B = engine capacities (eg 2000cc)

Then in another sheet,
with the inputs in A1: toyota, B1: 2000cc

Put in C1, then array-enter** the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=INDEX(Sheet1!C$1:C$100,MATCH(1,(Sheet1!$A$1:$A$100=$A1)*(Sheet1!$B$1:$B$100=$B1),0))

The above will return the value from col C in Sheet1 for toyota, 2000cc.
Copy C1 across to E1 to return the corresponding values from cols D and E of
Sheet1. C1:E1 can be copied down to return accordingly for other sets of
input values in A2:B2, A3:B3, etc. Adapt the ranges to suit.

**Done correctly, Excel will wrap curly braces: { } around the formula (look
for these braces in the formula bar as a visual check after you array
-enter). If you don't see these braces, then the array-entering wasn't done
correctly.