Adding A Condition To An IF And Vlookup Forumula
Aug 28, 2009
Title: How do I add a condition to an IF and Vlookup Formula:
I need to edit a formula to include a condition. I attached an excel file for ease of explaining the problem. I want to say if $E$5 has “A to B” then F5 will automatically have BOR, but if E5 has anything else then the Vlookup formula applies.
I was thinking something along this formula:
=IF(AND($E$5<>”A to B”,ISBLANK($B5),"",VLOOKUP($B5,'Drop Down'!$D$1:$E$500,2,FALSE)))
I know I need to add “BOR” in the formula above somewhere
Where am I going wrong?
View 2 Replies
ADVERTISEMENT
May 6, 2014
I have the following which works just fine
=SUMPRODUCT(--(Table_owssvr[Migration Slot]>= (ActualMigrations!D$3-6)),--(Table_owssvr[Migration Slot]<= ActualMigrations!D$3),
--(Table_owssvr[Department]= $A6))
If I then add a condition ie
=SUMPRODUCT(--(Table_owssvr[Migration Slot]>= (ActualMigrations!D$3-6)),--(Table_owssvr[Migration Slot]<= ActualMigrations!D$3),
--(Table_owssvr[Department]= $A6), --(Table_owssvr[Is_Migrated]="TRUE"))
It returns nothing even though the Is_Migrated is TRUE
View 8 Replies
View Related
May 12, 2014
I'm currently using the following function: =COUNTIFS(Data[Quarter],"Q1",Data[Country],A3,Data[Sales],">2000") which works perfectly. However I'd like to add another condition "between 1000 and 2000".
I've tried this but I'm not getting any luck: =COUNTIFS(Data[Quarter],"Q1",Data[Country],A3,Data[Sales],AND("<1000",">2000")).
View 3 Replies
View Related
Mar 28, 2014
I have a code that searches in a column for a certain value, if it finds it, it copies this value to a range adjacent to it.
[Code] ..........
However, if the cell where the code is supposed to find a new value is blank it makes the range blank. But in that case, I wan't it to leave the range cells with their original value.
View 11 Replies
View Related
Jun 29, 2006
I have this formula in my spreadsheet =IF(ISBLANK(B2), "", C1+1). I need to add a "dash T" to this formula so it shows up in every cell. How and where do i write that into a formula?
View 2 Replies
View Related
Jan 8, 2014
Below is my function.
="Profit $"&SUM(IF(Sold!D15:D8998<>"",IF(MONTH(Sold!D15:D8998)=A4,Sold!H15:H8998)))
I would like to edit this function to also count profit only if Cell A15:A8998 in my Sold sheet has the words "Shipped". The text value Shipped is not a text value I entered, it is automatically written based on a Vlookup formula I created.
View 2 Replies
View Related
Nov 17, 2009
=IF(SUM(Q4)=0,"",MAX(0,G4-MAX(F4,Q4))/7) - to calculate number of days for year
=IF(SUM(TODAY()>G4),(G4-MAX(F4,Q4))/7,SUM(TODAY()-MAX(F4,Q4))/7) - to calculate number of days year to date
I am using this condition to calculate the length of a service which falls with in a specific review period, eg
Service starts 1 Feb 09 and finishes 30 April 09
The review period is 1 Mar 09 and finishes 31 May 09.
I would need to measure the number of days that fall within the review period, which would be 1 Mar 09 - 30 April 09
At the moment I am missing a condition which identifies whether to subtract from 'service end date' or 'review end date'.
View 6 Replies
View Related
Mar 10, 2014
I'm preparing Quarterly Evaluations, and am having an issue because an Analyst has superseded an overall percentage goal. In the attached example, it is the overall evaluation with the 'problem data' in red D19 and highlighted.
I'd like to add a condition that limits the points in D19 for 'responsibility 3' to 150.
=IF(ISERROR(C19/B19)*(B10),"-",(C19/B19)*(B10))
*just a note for clarification, the other two responsibilities are a 'per hour' weekly goal, whereas the 3rd is an overall personal effort divided from the overall team effort.
View 4 Replies
View Related
Jan 22, 2010
Adding condition to SUMPRODUCT causes result to go up? (2007). I have the following formula:
View 5 Replies
View Related
Jul 12, 2009
I have a spread sheet I use to track tools. One tool may be checked out / returned several times a week. When I do custom sorting I end up with the Tools listed neatly by date and number. EX: ...
View 10 Replies
View Related
Oct 6, 2011
I am using the formula below to count the occurences of relevant text strings (names) in a cell that can contain many separate strings. It works great. "References!$F$2:$F$34" contains my list of names and the formula returns how many occurences there are in each cell.
What i'd like to do know is work in a date criteria. Each name has an associated relevant date attached to it as does each cell that I am looking for these names in.
So...in english what Id like to do is alter my formula so that when it finds a text string that exists in "References!$F$2:$F$34", it then compares the associated date in "References!$G$2:$G$34" with the date associated with N3 which happens to be O3.
SUMPRODUCT(COUNTIF(N3,"*"&References!$F$2:$F$34&"*"))
View 5 Replies
View Related
Dec 17, 2011
I've done this before but can't remember how I did it:
1BCDEFGH21234533red92701131096601005096604green20070582305250044940472805
blue0355203912033930389706bpink51059230632205352061280789-In column H,
I want to sum only rows that are less than or equal to cell H2.....
View 2 Replies
View Related
Aug 13, 2008
actual revenue planned revenue
5 4 125%
3 4 75%
-5 -3 -66%
-3 -5 60%
I need to get to those percentages (or close) the tricky part is when the numbers become negative. the forumla is actual revenue over planned revenue. If the actual revenue is below 0 and less than the planned revenue number than the attainment % should be a negative. However, if the actual revenue is below 0, BUT LESS THAN PLANNED revenue the percentage than becomes positive.
View 9 Replies
View Related
Aug 22, 2013
The issue is the I want to put the condition here in my the code that only copies the desired cells if the sheet is named specifically such as "Jan", if not named as this the worksheet should not be used. The current code I am using was posted on this site in 2009: VBA to copy specified cells from all the files in a folder
My modified code is:
Option Explicit
Public strSourceFldr As String
Public EachFile As Object
Public objFSO As Object
Public objFolder As Object
Public objFile As Object
Public strSheetName As String
[Code] .........
View 1 Replies
View Related
Oct 4, 2006
I would like to apply different conditional formatting at different times with a click of a button. I setup a dummy and turned on the recorder and recorded this
Range("A7:N7").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$C7=1"
Selection.FormatConditions(1).Interior.ColorIndex = 37
Range("A1").Select
End Sub
I tried changing to this
'/Conditional Format - OTHER EXPENSE B/L
Set rngConditional = wsData.UsedRange
With rngConditional...............
View 9 Replies
View Related
Feb 4, 2009
Trying to figure out how to write a formula to rank values in B if the values in A are the same? I tried rank(if(A2:A17=A2,B2:B17))but this did not seem to work.
View 9 Replies
View Related
Dec 19, 2013
potential VLOOKUP (or possibly another function)
I have a table with about 14 columns, and need a VLOOKUP based on column A.
The problem is Column A has 2 of each unique ID - but column B is the subtype.
For example
Column A Has names (Bob, Jim, Steve, etc) Column B has Opt in or is blank (if they opt out the cell is blank if they opt in the cell says Opt in)
So essentially it's like a vlookup - as i need to read across the columns based on the name, but i also need to check column B if they have opted in.
View 1 Replies
View Related
Jan 28, 2014
See attached the excel for reference : Vlookup with condition.xlsx‎
I have table A1:E10 , where i enter Internal team transfers "From Team" to " To Team" with Month
Now what i want in Table G1:S15 is that whenever a transfer takes place, for that particular month, tables gets filled up as +1 for "To Team" and -1 for "From Team." Also if there are multiple transfers happening for a particular team in a month then that numbers should sum up.
For your easy understanding, i have filled the table as per my logic explained above.
View 7 Replies
View Related
Jul 6, 2009
I've always used event driven functions - ie. user clicks a button and my function gets called.
What I want to do now is replace a HUGE formula that is impossible to debug with a function. So the idea is, any time the user changes any cell, the function should be called and update a certain cell with a new value.
View 9 Replies
View Related
Nov 22, 2008
I'm trying to sort out an accounts receivable ageing into various age categories For example, for a customer with 3 months credit term, i've used the formula =SUMIFS($C3:$AA3,$C$1:$AA$1,">=4",$C$1:$AA$1,"<=9") to sum up amounts that are 1 to 6 months past due. For the next age category 7 to 12 months past due i've used =SUMIFS($C3:$AA3,$C$1:$AA$1,">=10",$C$1:$AA$1,"<=15").
As my customers have different credit terms (30 days, 60 days etc), i've to adjust the above formula accordingly e.g. instead of ">=4", I'll have to change the criterion to ">=7". This is time consuming as i've got a long customer list.
View 2 Replies
View Related
Apr 10, 2014
I have an excel file with thousands of rows and I'm trying to pull certain data from one tab to another. In the example, I want to pull all Listings that are "Open" (I know, just filter), but if the listing is "Open" I want to pull all of the other statuses it once had as well in the new tab. Notice how a listing can have multiple statuses.
I was thinking I could filter by Status (Open) and then do a vlookup (using the listing number as the lookup number), but it doesn't work.
View 4 Replies
View Related
Jan 9, 2009
I've been trying to figure out how to do a vlookup using two conditions. Reading some other posts, I saw this could be done by combining a MATCH function but I'm still having trouble. Here is what I'm trying to do: I am trying to match cash amounts based on two look up values....one being the identifier (cusip) and one being the pmt code. http://i105.photobucket.com/albums/m204/RAK_08/data.jpg
This data is formatted exactly the same but comes from two different sources. I need to match the pmt amounts for a cash reconciliation.
View 4 Replies
View Related
Jan 28, 2009
i looking for function, which will give me a results if its the person good , bad and other (Y). It will look based on column A, but if will be more equal names in this column, it looks in the column surname (B) and return value from column (Y) in this row. But it can be more equal column B.
Here is an example:
Results what i want are in Z1,Z2,Z3
X Y Z
1 sue doe good
2 joe bond very good
3 mark no bad
SOURCE table
A B C
1 john doe good
2 joe black bad
3 sue doe good
4 mark black not bad
5 joe bond very good
6 mark no bad
7 kat doe bad
View 4 Replies
View Related
Feb 12, 2010
I am trying to display both text and a date in the same line. Currently I have something like ="The Date Is " & A1. where A1 is a cell with a date formatted in a preferred format. However, once I call reference to the cell, excel re-formats it into the serial date, so it displays something like. The Date Is 40220
instead of something like: The Date is 2/11/2010. Does anyone know how to format the number back into a date form? Just going through the format menus and setting it to date does not change it.
View 2 Replies
View Related
Oct 17, 2008
when he copies some data across a worksheet - the new data has a bold format, so he has to unbold everytime.
I can't see any obvious reason why this is happening.
View 9 Replies
View Related
Apr 26, 2014
I have an excel sheet with lot of data.i want to use vlookup function for getting what cell i want. the same time it will also check if and only if two cells are matching then return the result.is it possible. for example:
101 3.2 7.3 200 500
102 5.2 8.3 200 450
103 3.2 7.3 200 750
101 7.3 7.3 200 450
102 7.3 7.3 200 500
103 7.3 7.3 200 600
if my lookup_value is 103(ie A3 ) in this table array and required column is 6th column(ie E6).
return the value if and only if B & C cells of the corresponding row matches. In the example B6=C6 then return the E6=600.
if i am using without this condition it returns the same row values ie E3=750 required is E6=600
View 12 Replies
View Related
Feb 22, 2009
Have following table:
No. Be Country
1 D GB
1 C DE
2 B DE
2 A GB
I want to present it as follow:
GB DE
1 D C
2 A B
Did not solve it with a pivot table. Not either with VLOOKUP combined with IF. I am thinking in the direction: If 1 in the first column and GB in the third, return the D. But I am not getting it right.
View 2 Replies
View Related
Feb 16, 2014
I created a Vlookup but first want a condition to be met to determine what Vlookup to use.
Such as
If cell = 1 then
run Vlookup #1
If cell = 2 then
run Vlookup #2
There are only 4 possible variables that the cell could equal. This would have to be on a loop, because the entries in the worksheet are multiple. It would first see what the specific cell equaled, then determine what Vlookup to use.
View 1 Replies
View Related
Dec 11, 2008
How would I modify a (very long) array formula, while preserving the brackets? Or re-creating them? I've tried doing it "at the cell" without luck.
View 2 Replies
View Related
Jul 31, 2009
I have ~500 rows of data in columns A, B, C that is as follows, for example:.............
I am having trouble with coming up with a formula that will add up the total items shipped for each item. For example, Apples = 62 items shipped.
View 2 Replies
View Related
