VLOOKUP Based On Two Conditions?

Aug 16, 2013

My challenge is pulling in a data element from a file to an exisiting file based on two criteria.
Example: give me the value in cell e1 if cell b1 matches AND

File 1
A B C
1 H12377 03/05/2013 123
2 H12377 03/27/2013 276
3 H32389 05/03/2013 335

File 2
H12377 03/27/2013 _________

how do I get C2 value from file 1 (276) into file2 since column A is not unique but column A plus Column B is unique?

View 5 Replies


ADVERTISEMENT

Vlookup And Multiple Conditions?

Dec 17, 2013

Is VLOOKUP suitable for if one wants to lookup a value from a defined range based on two assumptions?

Enclosed is an example file.

I want to lookup the values in sheet1 in the defined range in sheet2 and write enclosure and density to sheet 1

what method to use?

nicolaiBook1.xlsx

View 6 Replies View Related

Multiple Conditions For VLookup Value?

Jan 8, 2009

ABB
MetricsCategoryJan 2008VisitsTravel50view_offerTravel10

Above is the data I want to lookup on. I need to be able to do a lookup for travel visits and a seperate lookup for travel view_offers.

Is there someway to do this with a vlookup or maybe match index?

View 9 Replies View Related

Vlookup Across 2 Sheets With Conditions

Apr 12, 2008

I have found that excel can be the best thing ever and also a nightmare when you are stuck on something. I really apprecciat anyone who can help me out.

In an excel workbook i have a sheet that has rows and rows of Sales Leads.

In column A i put a letter "C" if they have turned into a client or an "X" if the sale was not made.

I am looking for a function that will look at column A and when it sees the letter "C" then copy the entire row across and put the row into sheet 2.....

View 9 Replies View Related

Insert Vlookup Depending On Certain Conditions?

Feb 9, 2014

I am trying to retrieve data from another file using the VLOOKUP function however this is only to happen depending on if any of the 3 items of data appear in column 8(H)

OLY
OLY - QUO
OLY - PRO

I have the following and know this is not correct

Sub BlockAllocationsVlookupAll()
Dim x As Long For x = 1 To 65536
If InStr(1, Sheet1.Range("$H$" & x), "OLY") > 0 Then Sheet1.Range("$I$" & x) = Sheet1.Range("$I$" & x) & "sometext" End If Next
End Sub

I know the above doesn't do exactly what needs to be edited to include the Vlookup below

=VLOOKUP(A21,'[001 - Allocations - Blocks.xls]CurrentDayAll'!$1:$65536,9,FALSE)

The other issue is that the cell the VLOOKUP points to first will also change due to the varying length of the report

View 2 Replies View Related

Multiple VLOOKUP Conditions And MAXIMUM Value

Aug 16, 2009

I have written a snooker scorebaord spreadsheet which keeps a history of highest scores and highest breaks, but am unable to find a way of showing these key values in a table.

Attached is a cut down version to demonstrate the problem. If you can provide a solution it can be either in formula or code.

View 3 Replies View Related

Vlookup- Multiple Conditions For Lookup Value

Jan 8, 2009

ABB
MetricsCategoryJan 2008VisitsTravel50view_offerTravel10


Above is the data I want to lookup on. I need to be able to do a lookup for travel visits and a seperate lookup for travel view_offers.

Is there someway to do this with a vlookup or maybe match index?

View 9 Replies View Related

Vlookup With Conditions To Find Multiple Entries

Feb 2, 2009

I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/2008-1/15/2008 and $20 for 1/16/1008 - 1/31/2008.

A B C D
999 1/1/2008 1/15/2008 $10
999 1/15/2008 1/31/2008 $20
998 2/1/2008 - 2/25/2008 $15

I have another table (table2) in another sheet in the same workbook have a material and date.

A B C
999 1/10/2008
999 1/20/2008
998 2/15/2008

My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2.

I have tried using vlookup but it only works for the first match and doesn't check for other values

below is the function that i tried

=if(and(vlookup(A2,Sheet2!A1:D4,2,false)<=Sheet1!B2,vlookup(Sheet1!A2,Sheet2!A2:D4,3,false)>=Sheet1! C2)),vlookup(Sheet1!A2,Sheet2!A2:D4,4,false),"error")

View 14 Replies View Related

Nesting Multiple Vlookup Conditions In An If Funciton

Sep 24, 2009

I am not sure if the VLookup or the If function will be the best way to perform this tast but I think it is they way I would want to go.

I have attached a sample workbook of what I am working with.
Please refer to it.

On sheet 1 I named the table array Ear_Tag.

This is information that is provided to me from the farmers.

Note that there are sometimes more then 1 pig with the same tag number. The only way to tell them apart is by the Test Date.

On Sheet 2
Is where I record the test results.

I dont want to have to look up each pig after each test and fill in the rest of the cells manually. I would like to have a look up of the ear tag and the test date to find the correct pig. Then each of the cells will fill in the correct information.

View 6 Replies View Related

Sum Based On 2 Conditions

Dec 1, 2007

I want to have SUMIF and IF functions to be combined. i tried using the formula SUMIF( Timesheet!D2:D55,B2,Timesheet!F2:F55) but i want to edit the range everytime.
Can i have formula like if name = Ant merce and prj = Implementation support, then the sum will be 15 for the sumrange of Timesheet!F:F (entire F column)

View 4 Replies View Related

Average Based On Conditions?

Jul 1, 2014

I have attached a file where there is information in the sheet Weekly with the first row showing the same month for several weeks. add a formula in the sheet Monthly which would calculate average for each month based on the first row in Weekly sheet. Plus the grouping in the Weekly sheet is done by shops and in the Monthly sheet by food, which would I imagine make the formula more complex.

View 5 Replies View Related

Lookup Based On A Few Conditions

Mar 13, 2008

I am wondering if I can do a formula that would tell me if the name that appears in column J or K appears more than once at the time/date slotted in columns A and B. Basically, I want to make sure that the name(s) in column J and K aren't assigned 2 different places at the same time slot (column B) on the same say (column A).

View 9 Replies View Related

Sum Based On Multiple Conditions

Aug 28, 2009

I am trying to sum numbers based on three counditions.

My problem the third condition needs to use a wild card. I have attached a sample file, in the last condition I am only wanting to sum numbers where the characters in the last column begins with bd*

The yellow cell contains the formula I am using.

View 4 Replies View Related

Sum Based Conditions In Different Sheets?

Dec 11, 2012

I'm creating stockportfolio's based upon historical stock data. I have 300 stocks with monthly observations. Stocks are assigned to a portfolio based upon their return in the previous 6 months. I need the top 10% stocks in terms of return in previous 6 months (so 30 stocks) in one portfolio and the lowest 10% stocks in terms of returns in previous 6 months returns (so again 30 stocks) in another portfolio. Each portfolio is just a sum of the returns of the stocks that are in there.

First sheet: rows contain the return a stock had in the 6 months before the month in the left column (so january 2006 contains the return a stock would have had from july2005 -december 2005). This is done for 300 stocks. Second sheet: contains the return the stock had in that actual month (so january 2006 just contains the january 2006 return) The third sheet is where I want to create the decile portfolio's. This means that for every month i am creating 10 portfolio's: Each month "portfolio 1" should contain the sum of the returns of the stocks with the 30 highest returns in the past 6 months. "Portfolio 10" should contain the sum of the returns of the stocks with the 30 lowest pas 6 month returns. (30 is 10% of 300: that's why it is called decile portfolio)

So I should sum things from sheet 2 with the condition referring to sheet 1 where the returns of the past 6 months are displayed. I am trying formula's like =SUMIF(RANK...) but am getting no results. Somehow excel should select the right stocks and sum the 30 returns. I'm totally lost after a lot of trials.

View 1 Replies View Related

Adding Based On Certain Conditions?

Mar 6, 2013

I am doing a financial analysis for a hospital. I want to know how to add something to a number based on conditions. Base pay is $300 for the first 10 patients. If more then ten patients >11-20 add 40 dollars per visit if greater than >21 add 50 dollars per patient

View 3 Replies View Related

Get A Result Based On Several Different Conditions

May 20, 2009

New to the forum and in need of a bit of help. Friend of mine is in here regularly looking for assistance and tells me this is the best excel forum around. Thought I would put it to the test with a problem that is doing my head in.

The following is a table which shows a salesmans sales. He or she has sold to 8 customers.

What I need to do is show in the commission column how much they should get paid for the additional products based on this criteria.

1. If the penetration is equal to or over 41%, and he has sold the additional product for max profit of 250 then he should get £40

2. If the penetration is less than 41%, and he has sold the additional product for max profit of 250 then he should get £25

3. If he hasnt sold the product for full amount then he gets 10% of the profit, regardless of the penetration ...

View 6 Replies View Related

Lookup Based On Two Conditions ...

Jul 13, 2009

I know that there are bunches of threads concerning lookups with multiple criteria, but I just can't figure out how to translate one to my situation. I want to return a value based on an item name which is in column A, and an operation which is in row 1.

The array from which I need to look up the value contains part number in column B, operation in column G, and the actual value I need returned in column H.

So I need to return something like this: ...

View 14 Replies View Related

Sum Based On Hundreds Of Conditions

Aug 3, 2012

I am tasked with making a matrix that I cant seem to solve.

I have solved it on a small scale using

=SUMPRODUCT($E$5:$E$19,--ISNUMBER(SEARCH(O11,$C$5:$C$19)),--ISNUMBER(MATCH($D$5:$D$19,{"One","Two","Three"},0)))

The issue is that I need where {"One","Two","Three"} is to contain between 3 and 334 conditions.

Basically we have a data table that contains "channels" that have multiple affiliates under it. Then we have a data table by affiliate by country and revenue... I want revenue by Country By Channel...

View 3 Replies View Related

Popup Based On Two Conditions

Sep 25, 2012

I have this vb script which basically states if a value in column G is less than 0 then show a message box as below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("G3:G371")) Is Nothing Then
If Target.Value < 0 Then
MsgBox "You have entered a negative figure - are you sure this is correct?"
End If
End If
End Sub

What I would like to do is extend this script so that another message box pops up based on two conditions

i.e. if a cell in column F shows string "accounts use only" AND value in the adjacent cell in column G is > 0 then MsgBox "you have entered a positive figure - are you sure this is correct?"

View 5 Replies View Related

Macro With If / Then Based On 3 Conditions

Aug 10, 2013

I'm trying to adjust my macro to fill the interior cell color of a row if there's a non-blank row above and below it (i.e. if there are 3 or more non-blank rows together, then fill the 2nd, 4th, etc. rows. If there are only 1 or 2 rows together, then don't fill the cells.

However, what I'm getting is filling ever other row, with this code. There seems to be an error in how I'm trying to use and If condition with 3 criteria.

Code:
Sub colorin()

Dim LastCol As Long
Dim r As Integer
r = 6
With ActiveSheet
LastCol = .Cells(5, .Columns.Count).End(xlToLeft).Column

[Code] ....

Here are some sample results (pretend cells with red text are actually cells with interior color, black text is an unfilled cell).

Rank
Name

1
Jones, Some

[Code] ......

What I'm trying to achieve is: (again, red text actually represents filled cells - can't get sample shot of actual filled cells to copy into forum post).

Rank
Name

1
Jones, Some

[Code] ...........

View 6 Replies View Related

Display Value Based On 2 Conditions

Nov 6, 2009

I have a workbook with 2 worksheets. The first sheet provides a summary showing the last date a person attended a specific course.

The second worksheet is a list of all courses, attendees, dates, and status. I need a function that will match the attendee and course from the Master and display the date from the master in the corresponding cell on the summary sheet....

View 9 Replies View Related

Lookup Based On 2 Conditions

Dec 22, 2006

In the sample that I have attached. I am trying to compare Control ID and Business Date in Sheet[Test] and Sheet[Perform], If they are the same, then populate in Sheet[Perform] Column Test Status with the corresponding row in Sheet[Test]. note that Control ID and Business Dates are not constants.

View 4 Replies View Related

Add Sheets Based On Conditions

Dec 29, 2006

i am not able to solve this problem.

1- I have 3 tabs in this worksheet with the data - Tabs A , Master Data and refernce Table

2- Suppose column K in the tab " Master Data " has a record or number, they can be repeated as the new data us added every month.

3- Reference table has the list of the same data record ( numbers ) .

Now the situation is if there is a new record in the column K of the master data sheet the Macro should do the following.

a- Check for the record in the reference table , if doesnt exist then add the record in the reference table

b- create another sheet same as the sheet " A" (duplicate of
A) and name it the new record number

b- Plug the new record number in the cell B2 of the new sheet

c - Copy the row 8 from ( D8 to O8 ) in the new sheet from (D8 to
O8)from sheet A

View 5 Replies View Related

Extract Certain Data Based On Conditions?

Sep 25, 2012

I created a master data sheet to enter data manually to analyze. It contains 20 columns, and rows(continue change, because we enter data all of the days). I need to extract data to another sheet, when the user enters data in the master data sheet, but with some conditions, when the column 6 contain the word ASQ, and then extract all of the data, but the new sheet do not contain 20 columns, because the data that contains the word ASQ have only 16 columns.

View 6 Replies View Related

Extract Certain Data Based On Conditions

Sep 28, 2012

I need to create a macro to extract data from sheet1 to sheet2.

The sheet1 contains 15 columns, and the data starts at row 5.
The sheet2 contains 15 columns, and the data starts at row 5.

We enter data all of the days, so we have like 500 rows in the sheet1.

Only 2 conditions to extract data from sheet1 to sheet2:

if the column9 of the sheet1 contains the word "NO" and column11 of the sheet1 contains the word "ASQ".

View 4 Replies View Related

VBA To Set Cell Value Based On Multiple Conditions

Jan 4, 2013

I am creating a document log that tracks all excel files sent and received.

I use RDBmerge to get the filenames and data from the file batches.

My current macro edits the data down to the last stage of data needed to create the log.

My example workbook shows the final stage of the RDBMerge Data "Rough_Data" and then the final data formatted needed for the Log "Final_Data"

Included on the "Rough_Data" worksheet is the Code log used to complete the "Final_Data" worksheet.

I have highlight the cell range on the "Final_Data" Sheet that I need to complete.

filenames can occur multiple times from the RDBmerge, so only one occurance of the filename is place in column B of the "Final_Data" worksheet.

The criteria is this:

A column on the "Final_Data" worksheet as been Named for Each of the possible "Record Types" from Column G of the "Rough_Data" Worksheet. ("A,B,C,D,E,I,O,P,Q,T,V,W,X,Y")

I then must look through the "Rough_Data" worksheet for the first occurrence (if any) of that record type for each filename on the "Final_Data worksheet. If an occurrence is found I then must look at the "Unique ID" & "Program" Columns of the "Rough_Data" Worksheet. The key (on the "Rough_Data" worksheet) is used to determine the code that is written to the corresponding cell.

i.e.
"Final_Data"Date
FileName
Field2
User
Direction
Method

[Code] .....

I would very much like to automate this process, as sometimes I am dealing with over a hundred files with 30 plus rows of data each.

View 9 Replies View Related

Hide Rows Based On Two Conditions?

Apr 3, 2013

I'm trying to hide rows in an excel sheet based on two stipulations. I want to hide the row if it finds a particular value in column B and a different particular value in column K, otherwise i want it to do continue looping until it has hidden all rows that meet both stipulations.

VB:
Sub hide_loop()
Dim bl As Excel.Range
Dim blrange As Excel.Range

[Code]....

Currently I have tried different approaches, like a Do Loop, but I could not make that work, and this seems closer. The problem I'm having right now is that with this loop it hides everything found in column B regardless of what is in column K. I suspect this is because of the code following the if statement.''

View 9 Replies View Related

Count Occurrence Based On Two Conditions?

Sep 24, 2013

I'm trying to create a calendar overview of transactions from a raw data list. the result should be an amount, based on the number of occurrences of a specific transaction type at a particular date. Thus, the counter shall be based on two conditions. I've tried COUNTIF, INDEX, MATCH and VLOOKUP combined but I don't seem to "go all the way".

View 1 Replies View Related

Finding Next Value Based On Conditions In Formula?

Jan 6, 2014

Current simplified situation:

Department A
Employee_1
Employee_2
Employee_3
(empty cell)
Department B
Employee_4
Employee_5
Department C
Employee_6
Employee_7
Employee_8
Employee_9
Employee_10

With the VLOOKUP function, I have looked up the row numbers of the departments. Subsequently, I'd like to find the last employee of that department, based on the department's name and/or the department's row number.

A department-typed cell is non-empty and never contains an underscore. An employee-typed cell is non-empty and it always contains an underscore.

The simple and non-sufficing formula is the VLOOKUP of the row number of next department minus one. But with adding, moving and subtracting departments rapidly, this is not an option.

View 4 Replies View Related

Conditions Based On Today Date.

Jan 18, 2009

I'm using 2007 but I need this to work with Older versions so I tried to combine the condition for red

View 14 Replies View Related







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