Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup or match?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup or match?
=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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup or match?
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup or match?
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup or match?
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup or match?
If you are going to use Vlookup you should specify your optional 4th argument
as 0 for an exact match... =VLOOKUP(B4,'Weeks NQCQ'!A1:B100,2, 0) -- HTH... Jim Thomlinson "KC" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I lookup when match has more than one value? | Excel Worksheet Functions | |||
Lookup Formula: Return 1st match, then 2nd match, then 3rd match | Excel Discussion (Misc queries) | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
Lookup or Match ? | Excel Worksheet Functions | |||
match or lookup? | Excel Worksheet Functions |