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")))))))) |
All times are GMT +1. The time now is 08:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com