# nested "If" fuction

J

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

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"))))))))

J

#### 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

J

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?

S

#### 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.

J

#### 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