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.

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

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.

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.

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.

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.