yogi_Set Up Computed Columns In A Response Table To LookUp Pairs Per User Spec

                                          Google Spreadsheet   Post  #1282
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul 13, 2013
user Robster8 (http://productforums.google.com/forum/?zx=n821th1q4nl1#!category-topic/docs/spreadsheets/J8IaEcPxFXw)

Looking for a more efficient and elegant formula to look up a pair

I have a large spreadsheet with registrations of over 500 participants for a conference.  The dataset (created from form postings) contains 48 columns of data for each participant.

We've added three columns to the spreadsheet

One (AW) contains a manually entered "Companionship" number (eg. M1, M2, F1, F2 and so forth).  

A second one (AX) is a "helper" column which merely counts the instances of the companionship number in the data and appends that number to the end of the Companionship number.  =AW2&countif($AW$2:AW2,AW2)

The third one (AZ) performs a lookup in the dataset for the matching Companionship and enters a companion.

  

=if(AU2="","",if(right(AX2,1)="1",vlookup(AW2&2,$AX:$AY,2,false),vlookup(AW2&1,$AX:$AY,2,false)))

AU is merely a column that is populated once the companionship has been assigned. If it's blank, the companion column (AZ) is left blank.

AY is the full name of the individual.

It's fairly complex, and I believe that the lookups are bogging down my spreadsheet. Is there a more elegant and efficient way to perform this matching of companionships?

Thanks,

Rob

--------------------------------------------------------------------------------------------------------------------------------------------------------------