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‎
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.
Is there a way to do a vlookup with two separate criteria?
View 7 Replies View RelatedIs 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
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?
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.
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
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.
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 RelatedI 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.
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.
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 RelatedIs 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.
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))
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
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".
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 RelatedI 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 RelatedI 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)
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.
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.
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?
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.
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?
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‎
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.
I have the following 'numerical values'
2510000.011
3010000.011
3510000.011.3
2518000.032.5
3018000.043
3518000.043.7
2530000.125.5
3030000.145.8
3530000.176.2
With the first two values as criterias, I need to find the 3rd and 4th value
Example: If I have the criteria as 30 and 1800 , I should get the result as 0.04and 3
See attached a sample from a larger workbook I am working on. What i would like to do is in the Rec tab column G, keep the references from columns L & M as the Table Array and Column Index Number. I have =VLOOKUP(F:F,L:L,M:M,0), I would like to have =VLOOKUP(F:F,whatever tab reference is in column L as table array,whatever number is in column M as index number,0). I have included what I would like the data to look like in coulmn H.
View 2 Replies View RelatedI'm fairly new to excel and trying to create a simple spreadsheet to provide a log of staff allocation. I've created a first a sheet displaying a table with a week of rota. The first column lists where the staff are placed, the first row the date and inbetween the staff member covering that date. There are 5 further sheets, one for each member of staff with a simple table - first column is date, second is allocation.
Back on the first sheet below the timetable is a further grid showing on the first column staff names, and below each date a formula =VLOOKUP( B$3,David!$A:$Z,2,FALSE) pulls information from the 5 sheets displying whether a memeber of staff is on leave or there allocation on that particular day.
The problem i'm having is trying to get the 5 simple staff sheet to pull data from the Rota ie. I need say B3 to look at the date in A3, find the same date on the Rota sheet, look for the relevent staff name below it (if present), and display the allocation from the first column on the same row as the staff member.
I've tried a combination of Vlookup with Match and Index with Match but nothing seem to work.
Would like to be able to use a vlookup formula that incorporates multiple search criteria. In C10, I have a specific rank listed. In C11, I have the supervisor listed. The formula needs to list the name of the agent in C12 based on the criteria in C10 and C11.
agent rank score supervisor
A B C D
1 leo 1 99 jim
2 bob 3 68 jim
3 sam 1 73 ann
4 tim 2 84 adam
5 alf 2 55 ann
6 dan 3 42 adam
7 will 2 79 jim
8 sara 1 91 adam
9
10 Rank: 1
11 Supervisor: adam
12 Agent: " "