Vlookup Worksheet Name As Criteria
Jan 3, 2007
Is there any way that a worksheet tab name can be used as part of vlookup? I have got a spreadsheet with many worksheets (the names of these worksheets are different account numbers eg 3010,3020 etc). I have then got a summary sheet which summarises the balances.For eg on the summary sheet, I have set up all account numbers (as per the worksheet tab names) in Column A: 3010 3020
In Column B, I have to manually pull in the balances from various worksheets. Is there a way this can be done automatically and save me time as I normally have to pull in balances from over 50 worksheets.
View 4 Replies
ADVERTISEMENT
Jun 8, 2014
I am attempting to place a formula in cell b3 in the attached spreadsheet on the "sheet to pull data into" tab 1 (cell is highlighted yellow) to retrieve value in the yellow highlighted cell in the "Sheet that contains data" which is on a seperate worksheet within the same workbook.
The cells highlighted red in the "sheet to pull into" tab (cells: a1, a3, b1, and b3) are the criteria that needs to be matched to those cells on "sheet that contains data" tab in order to ensure the value returned is correct.Sample of problem.xlsx <----see the attached sample spreadsheet.
View 2 Replies
View Related
Dec 15, 2009
I'm a novice Excel 2007 user and appreciate all the help I can get. I have a workbook with monthly worksheets in it. When a certain data Type is selected from a drop down menu in that monthly worksheet than I would like to have it automatically enter specific data (Name, Date, Eval, Type) copied to another worksheet (CC) in the same workbook. I have been manually entering the data so far. Another thing, some of the data will be entered into the Monthly worksheets and some will only be manually entered into the CC worksheet so it would need to accomodate both methods of data entry. Please let me know if I need to clarify. I have attached the workbood, too.
View 11 Replies
View Related
Mar 10, 2009
OK, two files attached in the zipfile, pricelist-half.xls and pricelist-full.xls
The background is that i exported a file from our warehouse system so we could update prices and re-import it. That is the pricelist-half.xls
After our staff had spent a few days working on it, it came to light that (as the name suggests!) its only about half the products that should have been exported that are on the list.
Hence the second file, pricelist-full.xls which as its name suggests is the full
product list.
What I need to happen, to make this as painless as possible, is somehow for the items/rows that are on the full list, but not on the half list to be inserted to the half list but also have a yellow background for their rows so they stand out clearly.
View 10 Replies
View Related
May 22, 2013
I have two sheets in one workbook; one has a list of names in column A (136 of them) and another is a master list with all the names of people who work for the company (over 2000). Sheet 1 and Sheet 2 respectively. I need to populate columns B-E in Sheet 1 with the data in columns B-E in Sheet 2, but only for the names that match in column A. I've tried applying filters but can't get to the data I need for some reason. The names are formatted the same way in both sheets (Last, First). In Sheet 2 the names are links but in Sheet 1 they aren't; not sure if that's important.
View 7 Replies
View Related
Apr 4, 2014
The attached sample spreadsheet shows two tables. The first contains sales information, the second exchange rate information for two currencies.
I want to use VLOOKUP OR INDEX/MATCH to use the information in A4 and E4 and return the correct exchange rate, using the exchange rate table, in cell F4.
As you can see, I can currently do this for 1 currency but not two.
Is there any way to make this work?
example.xlsx‎
View 6 Replies
View Related
Sep 10, 2009
I have data organised as follows:
Column A:Date
Column B: Fund Name
Column C:Fund Value
In D1 I have the phrase “Fund A”, in E1 I have the date 31/08/2009 and in F1 I would like to create a VLOOKUP formula that looks in the range A:C and returns the value of Fund A on 31/08/2009. Fund A has a value for many other dates too so the VLOOKUP would have to be able to accommodate both criteria.
View 2 Replies
View Related
Oct 22, 2009
Is there a way to do a vlookup with two separate criteria?
View 7 Replies
View Related
Nov 19, 2007
Is it possible to lookup first & last from sheet 2?
I know how to use 2 lookup values from sheet1 but not from sheet2:
=vlookup(B5&C5,???,3,false)
*BCDEFGH3Sheet 1***Sheet2**4FirstLastSales*FirstLastSales5JohnSmith**JohnSmith5006JohnNelson**JohnNelson3007JohnPeterson**JohnPeterson400
Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Jan 31, 2014
I know this is an oft repeated title, I can't get the syntax correct.
In the attached file (only 41 rows) I've tried the following:
in Col AJ4:AJ83303 (I did not attach the whole sheet, formula reflects bottom row of data)
=IF(AI6>0,1(X6=$AJ$1,1(VLOOKUP(P6,$AR$21:$AS$41,2))
returns #VALUE! (for col X as text????)
=INDEX($AR$21:$AS$41,MATCH(1,AI4:AI83303>0)*(X4:X83303=$AJ$1),0)
returns #REF!
COL AI IS NET AMOUNT
AJ1 IS "NR" TO MATCH IN COL X
In all cells in Col AJ I want the amount in col 2 of the index to be added to the net amount in Col AI.
Criteria 1 Where net amount is >0 on any cell in Col AJ
Criteria 2 Where Col X = N1 (which has NR entered)
Col P is where the lookup value for the Index is, how do I reference that in the index formula?
View 7 Replies
View Related
Feb 13, 2014
I have a requirement where I need to look up a resource hierarchy with the region and the product line and then decide the value from the mapping table.
May be I can explain more clear with the workbook example attached.
If a user belongs to Dev & QA * hierarchy, check the region, and correspondingly check the product line, on meeting all these select the value from the mapping table to fetch the expense value from Approval process column of mapping sheet.
View 5 Replies
View Related
Dec 23, 2008
I have an output table that looks like the followings:......
There is 1 input for the spreadsheet: What month? Enter 9, 10, 11 etc. I need it to pull from a data sheet that is set up as such:......
So effectively it needs to vlookup the name in column A, (A, B, or C), and then it needs to match that to the column that has the same name as the output table (Crit.1 or Crit.2 and the number of the month above it (10,11,etc). I attached an excel sheet to better demonstrate
View 2 Replies
View Related
Jan 13, 2009
I have tried the Vlookup formula, and also the index/match formules but I can't get my desired outcome. As you can see the setup is fairly simple. For turnover I want to lookup the values based on Period AND profit center This last bit is my problem, how to define a double criterium.
The idea is to choose the wanted latest estimate (cell B1) and that for turnover, discount etc the values are based on 2 criteria (periode and profit center)
I have also added the desired outcome to clarify my question.
View 5 Replies
View Related
Oct 27, 2009
I want to add another vlookup criteria into the statement below and im having some difficulty, its currently has two called "Name" and "QrtName", i want to add another called "PlaceName". I have a named range called "Data" for the data the vlookup options use. It is working fine other only i want to add the third vlookup option:
View 9 Replies
View Related
Oct 9, 2013
I need to look up data from a different worksheet based on two criteria: (Main worksheet)
Company (A1), Fund Name (B1) and Stage (C1)
A | X
A | Y
A | Z
B | I
C | X
C | I
A-C are company names, I, X-Z are fund names and i need stage data
In (worksheet 2) I have the raw data, where the data for company, fund and stage are. I need to import the data for stage into the main worksheet given if company A matches fund X. I tried sumproduct but that doesnt allow non-numerical data.
View 3 Replies
View Related
Aug 24, 2007
currently my formula is as follows:
=VLOOKUP(H42,POQntyTOTAL,5,FALSE). i need to add another criteria to this formula.
that is....i need to have it return column 5 from POQntyTOTAL, where H42 AND where E42 is found.
View 10 Replies
View Related
Oct 11, 2007
Is there any way to nest a vlookup within an If statement that is capable of looking through 1 column on a separate worksheet for any of the following words "BASE" "MID" or "TOP" and then return a serial number in a separate column?
View 9 Replies
View Related
May 12, 2014
I am trying to modify an existing nested vlookup formula to include one more condition. I attached the excel data file. There are two tabs:
Tab #1 - Performance
Column B (Email Send Date): can be a repetitive date, something like
Row 2. 4/25/2014
Row 3. 4/25/2014
Row 4. 4/25/2014
Row 5. 4/25/2014
Row 6. 5/2/2014
Row 7. 5/2/2014
Row 8. 5/2/2014
Row 9. 5/9/2014
Row 10. 5/9/2014
Row 11. 5/9/2014
Column F (Product ID): can be same product for different Email Send Date. For instance, Row 2 & Row 9 have the same product ID - 128 and Row 5 & Row 10 have same product ID - 131.
Row 2. 128
Row 3. 129
Row 4. 130
Row 5. 131
Row 6. 567
Row 7. 897
Row 8. 987
Row 9. 128
Row 10. 131
Row 11. 234
Column R: Units Sold - need to retrieve the units sold value from Column D - Units Sold in UnitsSoldOnlineVlookup tab.
The formula needs to lookup the Units Sold from a table in a different tab, named UnitsSoldOnlineVlookup. This table contains the following columns:
Tab #2 - UnitsSoldOnlineVlookup
Column A - Email Send Date
Column B - Product Description
Column C - Product ID
Column D - Units Sold
Before Product IDs were different for each Email Send Date and I successfully used this formula:
=IF(ISERROR(VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE)),0,
VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE))
Now I need to embed one more condition to this formula - lookup Units Sold for the Product ID as well as the email date:
lookup Units Sold for a Product ID for a corresponding Email Send Date in UnitsSoldOnlineVlookup table and return Units Sold into the corresponding cell in the Performance tab.
I thought to use MATCH function in addition to IF and ISERROR functions but I it doesn't work - I know it is wrong.
=IF(MATCH(B2,UnitsSoldOnlineVlookup!$A$2:$D$31000,0),
ISERROR(VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE)),0,
VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE))
View 7 Replies
View Related
Mar 25, 2010
I have a single page of consolidated data that I need to pull from. The data is formatted like the following
Column A Column B
Category
Subcategory 1 Data
Subcategory 2 Data
Subcategory 3 Data
Subcategory 4 Data
All in the same column. I need a formula that will look for a certain category and then a certain subcategory and then provide the number to the right. The Categories and subcategories are in Column A and the data I want pulled over is in Column B
View 4 Replies
View Related
Apr 14, 2009
Im just going to start with the tables that will help show what i am trying to do...
TABLE 1
File #Dateid
215?
211?
27?
26?
TABLE 2
File #StartStopid
29131
214162
2183
Ok so say i have these two tables...i want to be able to look at the file # of line 1 in table 1, go to table 2 and return the id where the date from table 1 is between the start and stop in table 2. ie...in that example it should return id "2".
View 6 Replies
View Related
Jul 18, 2009
The vlookup formula is only partly working on the attached sheet, but I cant find an explanation as to why it can look up some criteria but not others.
View 3 Replies
View Related
Feb 1, 2010
I would like to take a precise value from one table which corresponds to two different criteria. See the example attached with this message.
View 3 Replies
View Related
Jul 7, 2009
I have a problem with vlookup, some of the items in the lookup array are not unique and vlookup gets confused with multiple matches, however if I can use a combination of 1st and second columns as the criterion then it would result in a unique match.
Problem is I don't know how to acheive this in vba (but I can with the worksheet formulas). (attached is a sample worksheet)
View 5 Replies
View Related
Jan 22, 2009
In column I, there is a list of dates - month and day only
In column J there is a list of dates, month,day, and year.
--I----------------J
1/14----------1/14/2009
1/14----------1/15/2009
1/14----------1/16/2009
1/15----------1/17/2009
in column K, I am trying to setup a Vlookup statement that states if the month and day in column I = the month and day in column J then return the value in column J.
I have the following formula, but it just returns N/A.
View 6 Replies
View Related
Oct 1, 2011
I'm trying to create an Excel based dashboard for my company. One part of the dashboard is a Top 20 Accounts based on territory, region, or district. Each of these three areas has source data in a seperate table. I need the top 20 vlookup to pull from the territory table if I enter a territory name in cell B20, pull from the region table if I enter a region name, and so forth. There are 10 different regions, 2 different districts, and 51 different territories.
I'm thinking that the answer somehow involves an IF statement. e.g. If cell B20 equals a region name, then perform the vlookup on the region table. If it contains a district name, pull from the district table. Otherwise pull from the territory table. I'm also thinking I may want to list all of the territories, regions, and districts in their own seperate column so the formula can say something like "If cell B20 is found in column M, pull from the territory table, if it's in column N, pull from the region table.
View 3 Replies
View Related
Mar 30, 2012
I have one spreadsheet that contains employee overtime info per pay period which is bi-weekly, another spread that has their monthly production numbers. So...
Employee OT PayEndDate
John Smith 5 01/13/12
John Smith 7 01/27/12
Jack Jones 8 01/13/12
Jack Jones 9 01/27/12
John Smith 6 02/10/12
John Smith 3 02/24/12
Jack Jones 8 02/10/12
Jack Jones 10 02/24/12
And so on
On the other sheet
Employee Production Month
Jonh Smith 53 Jan
Jack Jones 75 Jan
John Smith 45 Feb
Jack Jones 80 Feb
And so on,
What I need to do is see the overtime hours per month, and the production numbers for the same month. Something like...
Employee OT Production Month
Jonh Smith 12 53 Jan
Jack Jones 17 75 Jan
John Smith 9 45 Feb
Jack Jones 18 80 Feb
I have a pivot table the sums up the OT by PayEnd Date but need to change PayEndDate to month, which I know how to do, but then add in the production any thoughts on how I could do this?
View 5 Replies
View Related
Jul 17, 2012
I am looking to pull out an outcome based on the user inputting two criteria, below is an example:
A
B
C
D
E
F
G
H
I
J
[code].....
So if the user inputted P1 into cell H2 and inputted 9 in I2, J2 would show Pass, but if they inputted P5 into cell H2 and inputted 9 in I2, J2 would show Fail.
View 5 Replies
View Related
May 5, 2007
I have a workbook with two sheets
sheet1(Invoices) is the master and sheet2 (GR) contains information that i need to put onto sheet1.
I need a formula that will allow me to lookup sheet2 based on the criteria from 3 columns on sheet1... (PO no, Part No, Qty)
At the moment I am using 3 different vlookups to achieve what I need
If there are records on sheet2 which matches sheet1 then I would like to have the word MATCH if there is a match and CHECK if there is not?
View 9 Replies
View Related
Jun 9, 2014
Getting a formula that will generate the corresponding rating attached to a row value and columns header as per below example:
EnglishSS1 EnglishSS2 EnglishSS3 Rating
10 20 30 3
20 30 40 2
30 40 50 1
Where my variables would be based on whether a person is under EnglishSS1 and getting a score of <=10, the resulting rating would be 3. I know this is doable by doing a vlookup with a range lookup value of false. However I have roughly 70 column headers and it will be a bit taxing to combine an If and Vlookup statement to address it.
I have attached a sample sheet for reference : Book1.xlsx‎
View 2 Replies
View Related
Jan 15, 2014
Have spent ages on this to no avail. Need the attached sheet to verfiy the values in the "Reconciliation" are correct when referencing the "Prices" sheet. I need column F (Reconciliation sheet) to lookup the "Code" in column B in the "Prices" sheet and then further look up the "Payment Frequency" (column D in the Reconciliation sheet) in the "Prices" sheet to verify that the price is correct.
Due to slight decimal point issues, if the value has a greater than 0.02 difference then display "ERROR" otherwise 0.
View 6 Replies
View Related
