Multiple Condition Vlookup
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
ADVERTISEMENT
Feb 6, 2014
I have been struggling with this formula for ages and have finally given up. What I would like to do is to do a lookup on the concatenated values of Province, Department and Initiative No, which has mutiple values in the status of milestone column, and then give the answer based on a condition.
The condition for this example should be, that if the returned status / or statuses of the initiative is all 1, then the value should be 1, if it's all 2, then the value should be 2, and so on.....but if the values returned from the status of the initiative is a combination of 1,2 or 3, then it should give me the answer 2. If 4 is part of the comination of values returned, then the value should be 4.
Zero - 0 should be excluded from the formula as it is part of planned values and not actual.
View 5 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
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 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
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
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
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
May 27, 2014
Attach below are 2 sample file and inside it consist of device,grouping and lb1,tb1,ab1. How do I use excel formulae based on 2 condition and set from vlookup with 2 condition file to vlookup with 2 condition 1 file like a vlookup.
View 11 Replies
View Related
Apr 10, 2009
In range C15:C45 I have names of some steps. Starting from column D, row 6 have a drop down with 6 possibilities. Now based on what is selected in row 6 (so cell D6, E6, F6 and so on), I would like to color certain cells from 15 to 45 in that particular column (certain steps that are applicable to option selected in row 6 of that column). And as month goes by, users do that in more and more columns.
Also, steps overlap between conditions that are selected in row 6 and I would like this to macro (or formulae) to be running so that as soon as user selects the condition cells are colored and then they can fill out only those cells. So, I couldn't figure out any way to do this by conditional formating as there are more than three conditions and range is not continuous.see attached file as an example of final output.
View 4 Replies
View Related
Dec 22, 2013
formula using if condition. I attach the sample file about my problem.
View 14 Replies
View Related
Jan 25, 2007
I am trying to sum based on a set of conditions including time. The formula works fine except when time is between 11.30 pm to 12.00 am.I have attached the sample which will make the scenario clear. I am not sure how to modify the formula to ensure it works when the time is between 11.30 pm to 12.00 am.
View 8 Replies
View Related
Jun 4, 2008
I am trying to write a formula, that looks at row A1:A6 (Country), and equals A11 (Country chosen), but also looks at Columns C:E (Month), and equals B10 (Month Chosen). I can then add the figures in C3:E10. However I want a YTD idea, and so ADD Month 1 through to Month chosen in cell (B10) for the relevan contry chosen (A11). Uptil now I have used SUMPRODUCT, but am only able to sum one column, and not the required numbe rof columns.
View 6 Replies
View Related
May 27, 2014
I got four product name in column & want to bring other product name equal to same same product in different colum
like if products name in column range is PRV IT should bring birkett in other colum
PRV SHOULD BE IN NEXT COLUMN AS BIRKETT
TANK " " "" WAM
INSTRUMENT " " " " AGI
HVC " " " BAILEY
and rest of should be Marston
I had put that formula but didn't work 100%
=IF(C5>="PRV",IF(C5>="TANK",IF(C5>="INSTRUMENT",IF(C5>="HVC","BAILEY","AGI"),"WAM"),"BIRKETT"),"MARSTON")
View 8 Replies
View Related
Jul 18, 2014
I have added this example:
Test lookup main supplier.xlsb
I have a list of 5000+ items that I need to check (only 6 of them in this example in the red table).
I have a list of 25000+ items in a lookup table (only 10 of them in this example in the blue table).
Each item can have more than one supplier, but has only ONE Main supplier.
I need VBA code that determines if the supplier in B1 already is a main supplier for that specific item in the lookup list (yes or no).
I tried filtering it but that appears to be too slow for 5000 x 25000 items.
I have tried with VLOOKUP and Match, but I do not know how to combine the two conditions.
View 7 Replies
View Related
Apr 16, 2006
in cell A1 and cell B1 I have a "yes", "no" or "maybe"
in cell C1 and D1 I have a number
if cell A1 and cell B1 "yes" multiply cell D1 times 2 (all results in cell E1)
if cell A1 "yes cell B1 "no" multiply cell D1 times 1.2
if cell A1 "no" cell B1 "yes" multiply cell C1 times 1.2
if cell A1 and B1 'no" multiply cell cell C1 times 2
if cell A1 "maybe" cell B1 "yes" multiply cell D1 times 2
if cell A1 maybe cell B1 "no" multiply cell C1 times 2
if cell A1 "yes" cell B1 "maybe" multiply cell D1 times 2
if cell A1 "no" cell B1 "maybe" multiply cell C1 times 2
View 6 Replies
View Related
Apr 30, 2007
You all were so great last week (thanks Ninja). This is a tracking form for a contest based on restaurant cost results (Page attached). I need a formula for if C2>D2, but less than 1/2%, give100, if C2<D2, give 200.
View 9 Replies
View Related
Jun 29, 2007
I have a worksheet where there are rows of information in a specific range (A5:G600). I'm using the conditional format of =AND(MOD(ROW(),2), COUNTA($A5:$G500)) to alternate the row colors. I would also need to have the information in column A formatted. Column A contains dates, and if the date is 90 days less than or equal to today's date (90 days or more old) I would need to format that also.
I've tried using both at the same time, but the conditional formats will override each other when applicable. Am I able to do this via conditional formats, or would I need to do one or the other in VBA?
View 8 Replies
View Related
Sep 18, 2007
I am trying to use an OR condition within a sumproduct formula. Why am I not getting correct results?
(see attached)
I want to sum all costs for each of my PO #s:
If the Rebate Type is IPT, Wireless, or Security, then the result goes in the "Fndtn Ext Cost" column
If the Rebate Type is IPT Advanced, Wireless Advanced, or Security Advanced, then the result goes in the "Adv Ext Cost" column.
The formula I am using is as follows:
=SUMPRODUCT(($A2=PO)*(OR(Rebate_Type=$F$3,Rebate_Type=$F$5,Rebate_Type=$F$7))*(Ext_Cost))
But for some reason, even though the OR statement is evaluating to FALSE (I tested it by itself), it's still summing ALL the extended costs for that PO #.
View 9 Replies
View Related
Apr 7, 2014
I have data like this in data have tab in my sample file
POSSIBLEREDYELLOWGREENPINKVIOLET
GRADE/GRADEA/CG/GA/GG/GA/A
GRADE/GRADEC/CG/GG/GG/GA/A
GRADE/GRADEA/CC/CA/AA/AC/C
GRADE/GRADEA/CG/GA/GA/GA/C
GRADE/GRADE-----
and i have this data in lookup tab in my sample file
possibleREDYELLOWGREENPINKVIOLET
COLORC/CG/GG/GG/GA/A
GRADEA/AC/CA/AA/AC/C
now i want to do like this
if B2 value (red column) of datahave tab is equal to B2 value of red column in lookup tab then B2 value (red column) of datahave tab is equal to A.
if B2 value (red column) of datahave tab is equal to B3 value of red column in lookup tab then B2 value (red column) of datahave tab is equal to B.
if B2 value (red column) of datahave tab is not equal either B2 and B3 value of red column in lookup tab then B2 value (red column) of datahave tab is equal to H.
[code]....
i tried normal Hlookup but i do not know how combine multiple IFs to solve my problem.
View 4 Replies
View Related
Nov 6, 2013
Is there a way to filter data based on multiple OR condition with a like parameter.
I have data which has near about 50000 rows and now i would like to filter on a header called "Activity Type" and would like to fetch result for a text which resembles "from ABC group" or "From PQR group" or "From XYZ group".
View 5 Replies
View Related
Feb 1, 2009
I have 4 IF(AND) conditions that work fine separately but I can't seem to combine them into one.
=IF(AND(H56="New",I56="RE",Q56<=5),”YES”,”NO”))
=IF(AND(H56="New",I56="NON",Q56<=1),”YES”,”NO”))
=IF(AND(H56="Renew",I56="RE",Q56<=3),”YES”,”NO”))
=IF(AND(H56="Renew",I56="NON",Q56<=2),”YES”,”NO”))
View 2 Replies
View Related
May 14, 2011
I am trying to round a number to next multiple of 10 with condition i.e. for example if number is 1230.56 then it should round off to 1230 but if the unit number is more than zero i.e. 1231.56 then it should round off to 1240.
That means the main number should round off to next multiple of 10 only if unit number is equal to or more than 1.
Example 120.11 should round off to 120
121 to 130
120.99 to 120
119 to 120 and so on
View 8 Replies
View Related
Feb 27, 2008
I have this problem on putting the right formula. Column A entry are dates corresponding to replacement of parts x at column B with running hours of parts x at column c respectively. the table will look more like this:
A B C D
1 jan 2, 2008 brake front 200 ?
2 jan 20 , 2008 clutch 150
3 jan 30, 2008 wheel front left 300
4 feb 2, 2008 brake front 50
5 feb 5, 2008 brake left 85
6 feb 15, 2008 clutch 300
the formula will first look at column B and look for the entry "brake front" and every time it matches the entry it then looks up the date and then displays the corresponding latest running hour value on column C at cell D1. D1 formula will be having a result of 50 as it is the latest running hour value compared to 200.
View 9 Replies
View Related
Mar 28, 2008
we can get if functions to return conditions, as in if so and so "a" etc... is it possible to return long statements, instead of letters, with fill stops and commas. i don't know if it would be appropriate if i put an example of what i was trying to do. i am not trying to be rude but this
will this work in c3
=if(b3=5,"Readily understands classroom statements, questions and instructions.Can usually understand short electronic texts without difficulty.",if(b3=4,"Understands most classroom statements, questions, instructions.Can usually understand short electronic texts with only occasional difficulty.",if(b3=3,"Can generally understand classroom statements, questions and instructions, but may need repetition.Can understand short electronic texts reasonably well.",if(b3=2,"Sometimes misunderstands classroom statements, questions, instructions.Has some difficulty in understanding short electronic texts.",if(b3=1,"Has noticeable difficulty in understanding classroom statements, questions, instructions.Has great difficulty in understanding short electronic texts.")))))
View 9 Replies
View Related
Feb 7, 2014
Certification and Training tracking.xlsx
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
View 6 Replies
View Related
Mar 17, 2013
I have workbook with multiple sheets with full of formulas. I need a excel macro which can convert formulas to values based on multiple conditions.
The formulas are in B2 and below.This formula provides results as soon as a value is entered in C2 and one among D2 and E2 cells (see attached excel)I want a excel macro to convert the values provided by formula to actual values as soon as results are obtained.Further, when user delete data in C2, D2 and E2, i want the formula to be working again since if a user enters different data in C2, D2, or E2 the value should get updated.
I found a similar post which addresses some part of my problem but works only with one condition. [URL] ....
View 8 Replies
View Related
Jun 4, 2014
My data set has a number of duplicate entries. But I would like to sort them out based on some conditions. Say for example my C2 code appears three time in the dataset. I would like to sort this multiple code using the time and i column.
Say for example, 871514 code appears three times in this dataset, and if this code appear within three years - time with a different (i column text e.g. public and private), I would like to exclude these three duplicate entries from my dataset. If this code appears within next three years from the date, but have same I column text (e.g.public versus public), I will keep them.
So each code will be considered based on three years of time and type of text in I column. If the code appears again later after three years, I will keep them.
So the codes need to be checked within three years time with i column text.
My dataset identifies the difference of dates between last entry and next entry.
View 1 Replies
View Related
