nested "If" fuction
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
---------------
Jacob Skaria
"Jadeyze" wrote:
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?
"Jacob Skaria" wrote:
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
---------------
Jacob Skaria
"Jadeyze" wrote:
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"))))))))
|