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.
0
Utf
4/26/2010 4:18:06 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
552 Views

Similar Articles

[PageSpeed] 44

=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
0
Dave
4/26/2010 4:36:15 PM
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.
0
Utf
4/26/2010 4:46:13 PM
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.
0
Utf
4/26/2010 4:46:14 PM
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.
0
Utf
4/26/2010 4:53:01 PM
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.
0
Utf
4/26/2010 4:56:01 PM
Reply:

Similar Artilces:

lookup tables
ok, i have a value, 15.086, which i want to look up in a table of values which are all integers, shown below. 10 12 18 22 33 Mathematically, the closest value is 18. However, when i use VLOOKUP it tells me that 12 is the closest, because it searches down the list, and not up. Is there a way of searching up the list, or both up and down, in order to find the true closest value? Regards, Phil Newman A bit long winded but =IF(ISNA(VLOOKUP(A3,$E$1:$E$5,1,0)),INDEX($E$1:$E$5,MATCH(A3,$E$1:$E$5,1)+1),VLOOKUP(A3,$E$1:$E$5,1,0)) The value to lookup (15.086) is in A1 and the table of values i...

Re: Using one of the LOOKUP functions
Hi everyone. I have done a search but not been able to find what I'm looking for so perhaps some kind person here may be able to give me some pointers. My dilemma is thus; I have a sheet with 11 columns of data in around 45 rows (the number of rows grows as more data get put in during the month) The first column contains people's names. At the end of the month I will sort all the data on the Name column. I then subtotal on these names and this data is contained within the 11th column. The data I want to find at the end of each month will be contained within the row that starts wit...

Location of arbitrary lookup
Hello, I am still blown away by Chip Pearson's formulas to find arbitrary lookups: It's GREAT!!! http://www.cpearson.com/excel/lookups.htm However, I would like to have a formula that would give me the "row number" of the result. In Chip's example, he is looking for the 3rd "Chip" reference giving the lookup value of 120. I would like a formula to get the row number 16 which is the location of the result 120. I have seen some array formulas giving the location for the Max (or Min) in a range, and tried to incorporate that in a new formula, without success. ...

LookUp Table
I want to monitor how much vacation time a person in the office has but we all accrue it at different rates. A person that has worked for the company for less than 5 years earns 2.5 hours per pay period, 5-10 earns 3.7 and more than 10 earns 4.7. Can I create a “LookUp” table based on a persons Date Of Hire to determine how much vacation hours to calculate from. There are many factors that would influence the choice of the best approach for this. Firstly, you will need a table with the values to look up, e.g.: MinYears MinutesPerPayPeriod 0 15...

Lookup with Random Data #2
Thanks, That works great -- PF ----------------------------------------------------------------------- PFH's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1288 View this thread: http://www.excelforum.com/showthread.php?threadid=26736 ...

Data Modeling:Lookup table and Main table:establishing relationshi
I am working on creating data model from existing database using MS Visio 2007 Profesional Edition. Existing database is w/o PK-FKs & I am working to create relational DB which enforces RI. I have a lookup table which contains language codes,used by main table. The problem ,I am running into, is that these languagecodes(from lookup table) are used by 3 columns in main table. So, I am wondering how can I enforce PK-FK relationship here. As in... language_code from lookup table is PK and it has to associated w/ column(s) existing in main table. Something like following: Lookup Table ...

lookup table to dynamically display photos
Hi folks, I have created an Excel spreadsheet that contains product data such as price, weight, case dimensions, UPC, etc... The sheet uses the VLOOKUP function, which makes reference to cells in a separate tab to display the product data in a "one-up view". The user simply selects the product name from a pop-up list at the top of the page and all of the appropriate data for that product is displayed below it. This one-up view makes an excellent sell sheet for our Sales Reps to use when making a presentation, but what they would really like is to have the a product photo diplayed on...

MATCHING COLUMNS
I HAVE A QUESTION ABOUT MATCHING COLUMNS IN EXCEL. FOR INSTANCE I HAVE TWO COLUMNS ONE IN A AND ONE IN B WITH ALL DIFFERENT VALUES. HOW DO I MATCH COLUMN A WITH COLUMN B? AN EXAMPLE IS BELOW A B 000824108498310 000824108378310 000825252525151 000824108378310 002020204582810 000824108448310 020202222225550 000824108460310 None of your values matches any value from other column! Regards, -- AP PS: ALL CAPS means yelling: please avoid! <HOOSICK@NYCAP.RR.COM> a...

Payee Matching #2
Is there a feature in Money 2004 that matches a Payee in downloaded bank statement against previous transactions against that Payee in the account register and automatically assigns a budget category, for subsequent acceptance and entry into the account? It seems to do this for some transactions and not others? ...

Matching Text Files
Is it possible to take the addresses from sheet 2 and add them to sheet 1. The data I work with is almost 50,000 records long or I would just copy and past, or sort, filter or something. I'm trying to find a vba way to match up the names from sheet 2 and then add the addressed to the same names on sheet 1. Any ideas? Thx in advance. throw excel out the window and use a DATABASE. Excel can't handle more than 64k records; and it'll bog down way before that. good luck, I strongly reccomend MS SQL SERVER. Tim, Try using a VLOOKUP formula, with False as the last parameter....

matching and finding data
hi, how do i match columnA in spreadsheetA with columnB in spreadsheetB to find the value in columnD of spreadsheetB ? I don't understand what you want to do. Can you please re-phrase this, and if you give an example of what you want to achieve then so much the better. Pete On Sep 8, 9:04=A0am, Lynn <moley_c...@yahoo.com.au> wrote: > hi, > > how do i match columnA in spreadsheetA with columnB in spreadsheetB to > find the value in columnD of spreadsheetB ? Maybe... =vlookup(a1,sheetb!b:d,3,false) or =index(sheetb!d:d,match(a1,sheetb!b:b,0)) Debra Dalgleish has lot...

Match, Index, Indirect, Offset
I often see these functions used together and used quite a lot but what practical application do they have? From reading the help files it is easy to understand the theory of what's going on but applying it in practice is something else. Sumproduct was a classic case in point here. Going by the help file, we are told that sumproduct multiplies corresponding values in arrays and then sums the result (yeah but so what). But who would have guessed from this that it's application is quite extensive as a filtering tool of a sort. ----== Posted via Newsfeeds.Com - Unlimited-Unrest...

Find both matched and unmatched entries
Hi, I have two tables that I wish to find: 1) find matched data - copy that into a separate table 2) find unmatched data - copy that data into a separate table Can I achieve the above results with one query? I have three fields in each table that I would like to compare against: Client Data Status Any assistance with this would be greatly appreciated... Cheers, GLT Hi Yes you can use an append query to do this quite simple BUT you would end up with the same data in 2 tables which be a mistake. Maybe you could post some more details of what you are trying to do (the end result) a...

how to find all matches in an array
I have a task list that in one column lists the person primarily responsible for it and in the 2nd column is the back up person. The 3rd column is the task. On another sheet I want to put in someones name and bring up all tasks they are responsible for and all the tasks they are backup for. What is an easy way to do this. Easiest way IMHO would be to use advanced filter http://www.contextures.com/xladvfilter01.html -- Regards, Peo Sjoblom (No private emails please) "Greg" <Greg@discussions.microsoft.com> wrote in message news:FA4CB596-6451-4569-81AF-24EC18C50180...

Name Matching
Hi when an email is sent to a miss-spelled address, how do I route these emails to a postmaster or admin account? Thanks Matt What version of Exchange? -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchange.co.uk "Matt" <mattremoveme@edirect.co.uk> wrote in message news:006001c3b506$6c6670f0$a301280a@phx.gbl... > Hi > > when an email is sent to a miss-spelled address, how do I > route these emails to a postmaster or admin account? > > Thanks > Matt Exchange Server 2003 Running on Windows Server 2003 >----...

Highlight exact match in column and count
I am using this CF to find my duplicates =COUNTIF(A1:A$10000,A1)>1. The next part I need is to count the number of duplicates (gray shaded & strikethough). I have tried a few VBA but I read they don't work with formula based CF's. I could also sort the gray shaded then count them with a helper column but ideally would be have one cell at the bottom of my 8000 rows that total my duplicates. 'to display the distinct items in the range =SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&"")) 'duplicate items in the range.. =COUNT...

Return all entries eventhough they're not matched
-- Allen Phailat Wongakanit The big white space is generally considered the place to describe your problem or question. Try changing your join to left or right which ever makes sense in your context. "ali" <ali@discussions.microsoft.com> wrote in message news:052C53BA-7E12-49A3-A62D-915C4A964084@microsoft.com... > > -- > Allen Phailat Wongakanit ...

How do I match a color from copied web logo to my text in publishe
I am using Publisher 2003. I am making an internal flyer for my company and I pulled their logo off the intranet and put it in the banner headline. I want to take 2 specific colors in that logo and make one the backround in the adjacent text box and the other the color of the text itself. Is there a way to sample the logo color(s) and have the computer autmatically match it or does it have to be done by trial and error? If the latter is the case, what is the best way to do it so as to minimize error? Thanks You can look at the hex number of the colors but there's no guarantee tha...

Integration to Purchasing Enter Match
Is is possible to write an integration to the Purchasing Enter Match tables? I was looking at the destination sources available and was unable to find it as an option. We use purchase orders and receive the PO's but wanted to write an integration to enter/match the invoices with the PO's. I didn't even see a PO destination, is it possible to write an integration to a PO? Thanks, Marie, There is an integration available for Receivings TRX Entry if you choose to do Shipment or Shipment/Invoice Type. However, As you said, there is no integration for Enter/Match Invoices in...

copy and match data from one worksheet into another via script?
Hi all I have two worksheets 2 columns each The first sheet has ID and Name Column for example: Code ------------------- ID[/B] NAM 193948 Michael 684588 John 535279 Luke 098734 Matthe ------------------- my 2nd sheet Code ------------------- ID [B]NAM Michael John Luke Matthe ------------------- How do i use the first sheet to match up the names on the 2nd sheet and ultimately copy the ID numbers onto the 2nd sheet? This is just a simplified example. There are thousands of entries. Thank you if you have ...

Forum Search doesn't return matches newer than about Oct 2009
Has anyone else encountered this? When I'm in any of these Office Online forums and do a Search For: The results I get never have entries newer than about Oct 2009. I used to search on my user name and found all my posts in a group, but not anymore. It doesn't find the ones for 2010. Any thoughts on what's happening or how to fix it? I put this here because I couldn't find a more general place to ask this. Thanks, John "DocBrown" <DocBrown@discussions.microsoft.com> wrote in message news:907C6783-ADAB-444E-AB24-636D8D7C9C21@microsoft.com......

Adding a new lookup in 4.0 (I must be missing something simple)
I want to add a sales rep lookup to a quote. I figure since all sales reps are users we could just rename the field to sales rep. No go. The client sometimes has other people like admins enter a quote for a sales rep, thus will need to track who entered it and the sales rep to the quote. Simple I created a N:1 with user to add the lookup field, but I can't find the field to add to the quote form and if I use a custom field I see sales rep on the left menu of the quote. What am I missing, is this functionality not in 4.0, can't believe that? I know I could use a created by...

Print output does not match print preview
Problem is unique to Visio - all drawings look good in print preview but only the bottom 5.5 inches of the drawing is recognized and printed. Is this Visio 2003 and an HP printer? A problem similar to that is fixed with Service Pack 1. -- Mark Nelson Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "needhelp" <needhelp@discussions.microsoft.com> wrote in message news:84845ED7-1F5C-4053-AA76-DF6B58FA0295@microsoft.com... > Problem is unique to Visio - all drawings look good in print preview but > only &g...

Lookup Field 2nd Column
When using a lookup field within a form to choose information within another table, how do you have the ID be the bound field but show a second field in the form for the user. For example. There is a table for Cities. The first field is CityID (AutoNumber) and the second is the name of the City. Currently if the user uses the lookup field in the clients form, they can see City in the dropdown list but the CityID is what shows when you choose a record. Any Ideas how to show City Name instead? -- Rose here are the settings you need for your combo box: in the properties...

Getting rid of matching records?
Instead of trying to do this in VBA I have a large excel file with 50'000 + duplicate postal codes. What I'd like to do is when ever a postal code is in the file more than once I'd like to delete all of instances of that postal code. e.g. N0N1J0 N0M1K6 N8Y7J4 N0N1J0 RESULT (remove N0N1J0) N0M1K6 N8Y7J4 Hi HotRod Use Data>Filter>Advanced Filter See this page http://www.contextures.com/xladvfilter01.html#FilterUR -- Regards Ron de Bruin http://www.rondebruin.nl "HotRod" <NOSPAM@youremail.com> wrote in message news:u5sNpGS$GHA.1172@TK2MSFTNGP03.phx...