Four-Column Index / Match w/ duplicates

**dohearn** · 08-12-2011, 03:29 PM

I have a spreadsheet with four columns

A B C D
Advertiser Industry / CreativeType / VideoType / Click Rate

I need a solution that will match a unique record for inputs A,B,&C and return the corresponding value in the final CTR column, D. I've tried an INDEX MATCH array combination, but the presence of duplicates is screwing things up (I think). For instance some sample lines:

Auto / Flash / Video
Auto / Rich Media / Non-Video
Auto / Rich Media / Video
Retail / Flash / Video
Retail / Flash / Video

I can't seem to find a way to make this work. Help is much appreciated, thanks.

**DonkeyOte** · 08-12-2011, 03:52 PM

You can use an Array - assume for sake of example that criteria A1:C1 on RESULT sheet and data stored on DATA sheet

=INDEX(DATA!D1:D100,MATCH(RESULT!A1&"^"&RESULT!B1&"^"&RESULT!C1,DATA!A1:A100&"^"&DATA!B1:B100&"^"&DATA!C1:C100,0))
confirmed with CTRL + SHIFT + ENTER

If you want to handle Errors (no match) wrap the above in an outer IFERROR wrapper

Originally Posted by dohearn

I've tried an INDEX MATCH array combination, but the presence of duplicates is screwing things up

Assuming the CTR values are consistent for duplicates there should be no issue... if they are not you would need to determine which of the duplicates you would want to "match"