Index/Match - Lookup based on multiple column criteria
 

I have a 4 column spreadsheet:
A B C D
Customer Type Class
00001 0 General
00001 1 Premium
00001 6 Full Service
00001 9 Retail
00234 0 General
00234 6 Full Service
00234 9 Retail
12345 1 Premium
12345 6 Full Service

I would like to be able to lookup Customer numbers in Column A with a Type
"1" and return a "Premium" Class for all Types.

The intended results in column D would be:

A B C D
Customer Type Class
00001 0 General Premium
00001 1 Premium Premium
00001 6 Full Service Premium
00001 9 Retail Premium
00234 0 General General
00234 6 Full Service Full Service
00234 9 Retail Retail
12345 1 Premium Premium
12345 6 Full Service Premium

Thank you in advance for your help.

Index/Match - Lookup based on multiple column criteria
 

Enter with Ctrl+Shift+Enter:

=IF(ISNA(INDEX($C$2:$C$10,MATCH(1,(A2=$A$2:$A$10)* ($B$2:$B$10=1),0))),INDIRECT("C"&ROW()),INDEX($C$2 :$C$10,MATCH(1,(A2=$A$2:$A$10)*($B$2:$B$10=1),0)))

HTH

"Slider" wrote:

I have a 4 column spreadsheet:
A B C D
Customer Type Class
00001 0 General
00001 1 Premium
00001 6 Full Service
00001 9 Retail
00234 0 General
00234 6 Full Service
00234 9 Retail
12345 1 Premium
12345 6 Full Service

I would like to be able to lookup Customer numbers in Column A with a Type
"1" and return a "Premium" Class for all Types.

The intended results in column D would be:

A B C D
Customer Type Class
00001 0 General Premium
00001 1 Premium Premium
00001 6 Full Service Premium
00001 9 Retail Premium
00234 0 General General
00234 6 Full Service Full Service
00234 9 Retail Retail
12345 1 Premium Premium
12345 6 Full Service Premium

Thank you in advance for your help.

Index/Match - Lookup based on multiple column criteria
 

Here's another one:

=IF(SUMPRODUCT(--(A$2:A$10=A2),--(B$2:B$10=1)),"Premium",C2)

Copied down

Biff

"Slider" wrote in message
...
I have a 4 column spreadsheet:
A B C D
Customer Type Class
00001 0 General
00001 1 Premium
00001 6 Full Service
00001 9 Retail
00234 0 General
00234 6 Full Service
00234 9 Retail
12345 1 Premium
12345 6 Full Service

I would like to be able to lookup Customer numbers in Column A with a Type
"1" and return a "Premium" Class for all Types.

The intended results in column D would be:

A B C D
Customer Type Class
00001 0 General Premium
00001 1 Premium Premium
00001 6 Full Service Premium
00001 9 Retail Premium
00234 0 General General
00234 6 Full Service Full Service
00234 9 Retail Retail
12345 1 Premium Premium
12345 6 Full Service Premium

Thank you in advance for your help.

Index/Match - Lookup based on multiple column criteria
 

So much better than mine ... but another lesson learned!
"T. Valko" wrote:

Here's another one:

=IF(SUMPRODUCT(--(A$2:A$10=A2),--(B$2:B$10=1)),"Premium",C2)

Copied down

Biff

"Slider" wrote in message
...
I have a 4 column spreadsheet:
A B C D
Customer Type Class
00001 0 General
00001 1 Premium
00001 6 Full Service
00001 9 Retail
00234 0 General
00234 6 Full Service
00234 9 Retail
12345 1 Premium
12345 6 Full Service

I would like to be able to lookup Customer numbers in Column A with a Type
"1" and return a "Premium" Class for all Types.

The intended results in column D would be:

A B C D
Customer Type Class
00001 0 General Premium
00001 1 Premium Premium
00001 6 Full Service Premium
00001 9 Retail Premium
00234 0 General General
00234 6 Full Service Full Service
00234 9 Retail Retail
12345 1 Premium Premium
12345 6 Full Service Premium

Thank you in advance for your help.