Vlookup Formula (2 Criteria)

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


ADVERTISEMENT

Vlookup Formula With Multiple Criteria

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

Vlookup Formula Is Working On Some Criteria But Not Others

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

Vlookup Formula That Incorporates Multiple Search Criteria

Oct 6, 2008

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: " "

View 2 Replies View Related

VLOOKUP With Two Criteria

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

VLOOKUP Using Two Criteria

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

Vlookup With Two Criteria

Oct 22, 2009

Is there a way to do a vlookup with two separate criteria?

View 7 Replies View Related

Vlookup Using 2 Criteria

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

Vlookup With Multiple Criteria?

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

Using Vlookup On Multiple Criteria?

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

Vlookup And Matching 2 Criteria

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

Vlookup / Index On 2 Criteria

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

Add Another Vlookup Criteria Into The Statement

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

VLookup Data With Two Criteria

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

Vlookup - 3 Unique Criteria

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

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 View Related

Vlookup With Multiple Criteria In Same Column

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

Vlookup With Unique Multiple Criteria

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

“Vlookup” Or “Sumif” With Multiple Criteria ?????

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

Multiple Criteria Vlookup In Vba Userform

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

VLOOKUP Multiple Criteria (Dates)

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

VLOOKUP Based On Multiple Criteria?

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

VLookup With Multiple Criteria And Calculations?

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

VLOOKUP Or HLOOKUP With Multiple Criteria

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

Vlookup Multiple Criteria On Two Lists

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

Index / Match Or VLookup For Multiple Criteria

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

Vlookup Using Multiple Criteria To Check Values

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

Multiple Criteria - Vlookup For Numerical Values

Aug 28, 2009

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

View 10 Replies View Related

Vlookup:keep Cell Reference As Lookup Criteria

Jan 7, 2010

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 Related

VLookup With 2 Criteria - Provide Log Of Staff Allocation

Aug 31, 2012

I'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.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved