lookup or match?


K

KC

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.
 
Ad

Advertisements

K

KC

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)
 
J

Jim Thomlinson

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.
 
Ad

Advertisements

J

Jim Thomlinson

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)
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top