lookup or match?
in [General]
|
From: KC on 26 Apr 2010 12:18 on tab: Weeks NQCQ I have a table as follows Col A Col B Col C Week CQ Week NQ Week wk2 1Q10 Wk-12 Week 2 wk3 1Q10 Wk-11 Week 3 wk4 1Q10 Wk-10 Week 4 wk5 1Q10 Wk-9 Week 5 wk6 1Q10 Wk-8 Week 6 wk7 1Q10 Wk-7 Week 7 wk8 1Q10 Wk-6 Week 8 wk9 1Q10 Wk-5 Week 9 wk10 1Q10 Wk-4 Week 10 wk11 1Q10 Wk-3 Week 11 wk12 1Q10 Wk-2 Week 12 wk13 1Q10 Wk-1 Week 13 wk14 2Q10 Wk-13 Week 14 On tab "Week at a Glance", in cell B4 I will put in the current week, lets say I input wk8, on the same tab, I need a formula to look at the table above and insert the corresponding CQ Week in Col B (answer should be 1Q10 Wk-6). I will need a similar formula in cell I4, that will grab the correct answer from column C above.
From: Dave Peterson on 26 Apr 2010 12:36 =if(b4="","",vlookup(b4,'Sheet2'!A:c,2,false)) and =if(b4="","",vlookup(b4,'Sheet2'!A:c,3,false)) This assumes you put the table on Sheet2 in columns A:C. Debra Dalgleish has lots of notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://contextures.com/xlFunctions02.html#Trouble KC wrote: > > on tab: Weeks NQCQ I have a table as follows > > Col A Col B Col C > Week CQ Week NQ Week > wk2 1Q10 Wk-12 Week 2 > wk3 1Q10 Wk-11 Week 3 > wk4 1Q10 Wk-10 Week 4 > wk5 1Q10 Wk-9 Week 5 > wk6 1Q10 Wk-8 Week 6 > wk7 1Q10 Wk-7 Week 7 > wk8 1Q10 Wk-6 Week 8 > wk9 1Q10 Wk-5 Week 9 > wk10 1Q10 Wk-4 Week 10 > wk11 1Q10 Wk-3 Week 11 > wk12 1Q10 Wk-2 Week 12 > wk13 1Q10 Wk-1 Week 13 > wk14 2Q10 Wk-13 Week 14 > > On tab "Week at a Glance", in cell B4 I will put in the current week, lets > say I input wk8, on the same tab, I need a formula to look at the table above > and insert the corresponding CQ Week in Col B (answer should be 1Q10 Wk-6). > I will need a similar formula in cell I4, that will grab the correct answer > from column C above. -- Dave Peterson
From: KC on 26 Apr 2010 12:46 Was able to figure it out....for anyone else that might have a question like this, the formula is: =VLOOKUP(B4,'Weeks NQCQ'!A1:B100,2) "KC" wrote: > on tab: Weeks NQCQ I have a table as follows > > Col A Col B Col C > Week CQ Week NQ Week > wk2 1Q10 Wk-12 Week 2 > wk3 1Q10 Wk-11 Week 3 > wk4 1Q10 Wk-10 Week 4 > wk5 1Q10 Wk-9 Week 5 > wk6 1Q10 Wk-8 Week 6 > wk7 1Q10 Wk-7 Week 7 > wk8 1Q10 Wk-6 Week 8 > wk9 1Q10 Wk-5 Week 9 > wk10 1Q10 Wk-4 Week 10 > wk11 1Q10 Wk-3 Week 11 > wk12 1Q10 Wk-2 Week 12 > wk13 1Q10 Wk-1 Week 13 > wk14 2Q10 Wk-13 Week 14 > > > On tab "Week at a Glance", in cell B4 I will put in the current week, lets > say I input wk8, on the same tab, I need a formula to look at the table above > and insert the corresponding CQ Week in Col B (answer should be 1Q10 Wk-6). > I will need a similar formula in cell I4, that will grab the correct answer > from column C above.
From: Jim Thomlinson on 26 Apr 2010 12:46 My preference is Index Match as it is less prone to developing errors than VLookup =index(B$2:B$100, match($A$2:$A$100, $B$4, 0)) You can drag that formula to the right to ge tthe reference to Column C. -- HTH... Jim Thomlinson "KC" wrote: > on tab: Weeks NQCQ I have a table as follows > > Col A Col B Col C > Week CQ Week NQ Week > wk2 1Q10 Wk-12 Week 2 > wk3 1Q10 Wk-11 Week 3 > wk4 1Q10 Wk-10 Week 4 > wk5 1Q10 Wk-9 Week 5 > wk6 1Q10 Wk-8 Week 6 > wk7 1Q10 Wk-7 Week 7 > wk8 1Q10 Wk-6 Week 8 > wk9 1Q10 Wk-5 Week 9 > wk10 1Q10 Wk-4 Week 10 > wk11 1Q10 Wk-3 Week 11 > wk12 1Q10 Wk-2 Week 12 > wk13 1Q10 Wk-1 Week 13 > wk14 2Q10 Wk-13 Week 14 > > > On tab "Week at a Glance", in cell B4 I will put in the current week, lets > say I input wk8, on the same tab, I need a formula to look at the table above > and insert the corresponding CQ Week in Col B (answer should be 1Q10 Wk-6). > I will need a similar formula in cell I4, that will grab the correct answer > from column C above.
From: Jim Thomlinson on 26 Apr 2010 12:53 Wrote may match backwards... =index(B$2:B$100, match($B$4, $A$2:$A$100, 0)) -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: > My preference is Index Match as it is less prone to developing errors than > VLookup > > =index(B$2:B$100, match($A$2:$A$100, $B$4, 0)) > You can drag that formula to the right to ge tthe reference to Column C. > -- > HTH... > > Jim Thomlinson > > > "KC" wrote: > > > on tab: Weeks NQCQ I have a table as follows > > > > Col A Col B Col C > > Week CQ Week NQ Week > > wk2 1Q10 Wk-12 Week 2 > > wk3 1Q10 Wk-11 Week 3 > > wk4 1Q10 Wk-10 Week 4 > > wk5 1Q10 Wk-9 Week 5 > > wk6 1Q10 Wk-8 Week 6 > > wk7 1Q10 Wk-7 Week 7 > > wk8 1Q10 Wk-6 Week 8 > > wk9 1Q10 Wk-5 Week 9 > > wk10 1Q10 Wk-4 Week 10 > > wk11 1Q10 Wk-3 Week 11 > > wk12 1Q10 Wk-2 Week 12 > > wk13 1Q10 Wk-1 Week 13 > > wk14 2Q10 Wk-13 Week 14 > > > > > > On tab "Week at a Glance", in cell B4 I will put in the current week, lets > > say I input wk8, on the same tab, I need a formula to look at the table above > > and insert the corresponding CQ Week in Col B (answer should be 1Q10 Wk-6). > > I will need a similar formula in cell I4, that will grab the correct answer > > from column C above.
|
Next
|
Last
Pages: 1 2 Prev: Fill/Expand Selection adding placeholders to Decimals Next: Excel VBA AutoFilter code |