nested "If" fuction

[Jadeyze]

I ranked the truckers by price. No Problem, Now I want to show what trucker
placed 1st, 2nd or 3rd. not much of a problem but I have more than 7
carriers so I can't use the below formula.

How can I tell Excel to look across a row, determine which cell has a number
(1,2,3,4,...) and based on the number in the cell type the column
header(truckers name)

Dest. City ST Quality Cox Barr Trans
ABBEVILLE AL 1 3 2
IRVINGTON AL 3 1 #N/A

So the result should look like this:
Primary 2nd Choice 3rd choice
ABBEVILLE AL Quality Barr Trans Cox
IRVINGTON AL Cox tie Quality

=IF('Mapleton Ranking'!F113=1,'Mapleton Ranking'!$F4,IF('Mapleton
Ranking'!G113=1,'Mapleton Ranking'!$G4,IF('Mapleton Ranking'!H113=1,'Mapleton
Ranking'!$H4,IF('Mapleton Ranking'!I113=1,'Mapleton Ranking'!$I4,IF('Mapleton
Ranking'!J113=1,'Mapleton Ranking'!$J4,IF('Mapleton Ranking'!K113=1,'Mapleton
Ranking'!$K4,IF('Mapleton Ranking'!L113=1,'Mapleton Ranking'!$L4,IF('Mapleton
Ranking'!M113=1,'Mapleton Ranking'!$M4,"TIE"))))))))

 

[Jacob Skaria]

In the same cell try the below to get the 1st choice..replace 1 with 2,3 for
the rest

=INDEX('Mapleton Ranking'!$G4:$M4,match(1,'Mapleton Ranking'!G113:M113,0))

If this post helps click Yes

 

[Jadeyze]

Jacob:
Thanks! This seems to work, but when I try to copy to the other cells (I
get a #REF! message - see below. I can't get it to keep the cell reference
F3:R3 for the truckers names.

=INDEX('Mapleton Ranking'!#REF!,MATCH(1,'Mapleton Ranking'!$F12:$R12,0))

After I can get the first choice I know to change tweek the formula for 2, 3
etc.

Is there an easier way to do this or do I need to go into each and every
cell and manually change?

 

[Sean Timmons]

that would be because you moved up 101 cells, so 4 becomes -97. Assuming
you'll want $F$4 , $J$4 and such. That will lock down row 4 for all those.

 

[Jacob Skaria]

Use absolute referencing as below. Below are the different reference styles.

=INDEX('Mapleton Ranking'!$G$4:$M$4,match(1,'Mapleton Ranking'!G113:M113,0))

A1 Relative referencing. Both column and row will change if you copy or drag
the formula.
$A1 The column reference is fixed and will not change
A$1 The row reference is fixed and will not change.
$A$1 Column and row reference are fixed.

If this post helps click Yes