VLOOKUP Multiple Values Then Give Answer Based On Condition?
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
ADVERTISEMENT
Dec 27, 2012
I am trying to created a spreadsheet for work where I have created to validation drop down boxes, one each box has been selected i want it to return back with the correct answer in the 3rd column.
below are the 3 colums. i have created a validation for column 1 and 2 but when selected i want the final box to = column 3 ie. >=9, =2
120%
12
>=2
130%
13
>=2
140%
[code].....
View 9 Replies
View Related
Nov 6, 2011
I created an UserForm that looks for Student Name his/her Conferenece Date through a VLookup.
The following is my code:
Private Sub cmdFind_Click()
With frmConference
.txtTranslator.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 6, 0)
.txtDate.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 5, 0)
.txtTime.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 4, 0)
End With
End Sub
The txtTranslator and txtTime works correctly, however txtDate give me a number '40850' not a Date?
How can I change this number for the actual Date?
View 9 Replies
View Related
Feb 11, 2010
I'm trying to sum across multiple worksheets based on a simple condition. I have a time sheet with a worksheet for each week (52 worksheets). I've gone back and added a column to code the type of work done, types 1 - 17. I want to sum the hours of each type across all worksheets. This is what I have that doesn't work:
View 10 Replies
View Related
Sep 17, 2009
I have the attached worksheet with clients' names in four columns (A-D). The first names and last names must remain separate. I need one additional column that shows whether a full name (first and last) in C and D also appears in A and B.
Then one more column that shows how many times the name in C and D shows up, but only if it is also in A and B.
View 8 Replies
View Related
Oct 27, 2005
The following formula shoud give an answer of 178. I think I am placing the
parenthesis in the wrong position.
The answer I get is 308669
=(((F22*71449)*0.4)+16147/C22)
Cell-F22 number is 1826
71449 is a constant number
0.4 is a constant number
16147 is a constant number
Cell-C22 is 10.8
View 10 Replies
View Related
Nov 26, 2006
How can I get this to read each, (if (and statement) and give the correct answer. And not give me the 1st underlined statement answer in cell D10. If that also equals true.
But if any other cells G11 thru G14 equals OUT. It still will give the 1st statement.
I need the answer to be the only one of them correctly that’s equal true.
I know that the 1st underlined statement = true. Because G15= OUT .....
View 9 Replies
View Related
Feb 14, 2014
see the attached 'Example' excel sheet.
I'm not the best at explaining, but I think that the excel sheet is pretty straight forward.
In Sheet2, cells C3 and D3 are manually entered by the user. I am looking for a formula for B3 that will take the information entered into C3 and D3, find the row in Sheet1 in which column C=Sheet2!C3 and column D=Sheet2!D3, and give the unique corresponding result from Sheet 1 for that row in column B .
So, for example,
if C3=A and D3=102, then the result would be 444
if C3=A and D3=104, then the result would be 111
if C3=B and D3=102, then the result would be 111
The combination of C3 and D3 is always unique and will never have more than one possible result in column B of Sheet1.
View 5 Replies
View Related
Oct 7, 2006
Sheet 2 is the problem.
I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9.
Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section.
I have included a sample.
View 9 Replies
View Related
Mar 23, 2014
I have a set of 4 multiple choice answers B,C,D,E column "F" lists a # 1-4 which lets you know which column letter (b,c,d,e) the correct answer is in. I want to set up a formula that will copy the contents of the correct answer (based on that # identifier) and copy it into column "A" which is currently blank.
View 7 Replies
View Related
Mar 23, 2014
Here's an example of what i want to do
A_________ B Frank Sinatra C Sammy Davis D Dean Martin E Joey Bishop F 3
I want to put "D"'s value 'Dean Martin' in column A - i know it belongs in A because the "3" in column F indicates its the 3rd answer listed i.e Dean Martin. This format would be the same where there are 4 possible answers on the column to the right dictates which answer is correct.
View 2 Replies
View Related
Feb 15, 2010
I'm trying to make a simple chart with VBA based on a row with values that will color the offset cell interior red and also give it a value of 1. (look a the example sheet.)
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
Jul 11, 2012
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 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
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
May 18, 2007
im trying to get the lookup answer based on two values the current formula i have is
=VLOOKUP(AND(A1,B1),Sheet2!$A$1:$C$31,3,FALSE)
which naturally returns N/A... i might even be using the wrong formula?
View 2 Replies
View Related
Oct 4, 2009
I have data in A column.
I want to Get anser in B Column.
Sheet1 ABC1S.NoCountryCode2E8481234567CroatiaE8483E8351234568SloveniaE835472123456900Ireland725JM123457000LuxembourgJM Excel tables to the web >> Excel Jeanie HTML 4
Answer Based on C Column....
View 9 Replies
View Related
Jan 15, 2014
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
View 7 Replies
View Related
Apr 4, 2014
I need a macro I can run that will insert a row whenever Column A changes (from PPN A to PPN B to PPN C, etc.) and that will insert string values into the Cells as follows:
In all cases, the insert cell values into column A (PPN) will be the value from the previous cell and column B (CPN) will be 'LABOR'
Here's image of before and desired after:
Before:
PPNCPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02ADMIX
4AQ02AUTUMN BLEND
4AQ02OVERHEAD
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03ADMIX
4AQ03BETHEL BLEND
4AQ03OVERHEAD
After:
PPN CPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02ADMIX
4AQ02AUTUMN BLEND
4AQ02OVERHEAD
4AQ02LABOR -insert row and populate cells here
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03ADMIX
4AQ03BETHEL BLEND
4AQ03OVERHEAD
4AQ03LABOR -insert row and populate cells here
View 1 Replies
View Related
Oct 7, 2007
II want to sum the values under 'amount' column only if the corresponding value under 'Balance' column is "Paid"
NameAmount Start Date End Date No.of DaysInteresetBalance
Sat1 250 1-Aug-07 20-Aug-07 19 - "paid"
Sat2 550 4-Aug-07 20-Aug-07 16 -
Sat3 250 10-Aug-07 20-Aug-07 10 - "paid"
Sat4 450 6-Aug-07 20-Aug-07 14 -
Total PAIDAMOUNT(B1:B4)
For the above, the sum value should be 500. I have written the following function to calculate the same. On uncommenting the line 'paid = CStr(paidvar)' the value is displayed as '#VALUE!'. On uncommenting all commented lines, the value is displayed as 'Paid'.
Function PAIDAMOUNT(amountRange As Range) As Variant
Dim count As Long
Dim paidvar As Variant
Dim paid As String
Application.Volatile True
PAIDAMOUNT = 0
For count = 1 To amountRange.Cells.count
paidvar = amountRange.Offset(count - 1, 7 - amountRange.Column).Value
'paid = CStr(paidvar).................
View 2 Replies
View Related
Jul 13, 2014
I have an Inventory Workbook were Item codes in Left column and Date in top row. I am using the Vlookup formula Its displays only the first value. If their are repeated dates for the same Item code I want to sumup the value according to the Date for that Item code. find the attached sample file
I need to update the vlookup formula in the output, so that it takes the values accordingly and sumup.
I am not sure if I have explained this clearly; however, you will understand better if you see my attached file with examples of my expected output.
View 2 Replies
View Related
Feb 12, 2009
I am having problems with choosing multiple values for the second condition in the b range. The formula works for one account in the array, but not multiples.
=SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--('Data Sheet'!$B$2:$B$997={"300100","302100"}),'Data Sheet'!D$2:D$997)
View 3 Replies
View Related
Jul 19, 2007
I want use the formula sumif...something like that! =SUMIF(I77:I176;AND(>F1092;<F1091);Q77:Q176). I want sum the values between q77:Q176 if the value in i77:i176 is >F1092 and <F1091 But the multiple criteriun don´t work in the sumif.
View 8 Replies
View Related
Aug 21, 2007
I have a simple problem I am trying to find a set a values on a column and what to replace once found with the first 5 most left characters. Example:
in column A I can have text and numbers; the numbers that I want to find are in the following format xy:zw / xy:zw on each case the values can be from 0 to 9 so this is tons of posible combinations, I been trying to use **:** ? **:** but I don't get none. This is what I am doing
Set target = Sheets("Sheet1"). Range("A1:A9000")
For Each cell In target
If cell.Value = "**:** ? **:**" Then cell.Value = "00.00"
Next cell
A example would be 02:47 / 03:15 the next row could have 03:15 / 04:09
and so on however I could have text in some rows. In reality I would like to find and replace the cell.value with the Left 5 characters if the cell contains ANY value maching that format. Text in that column will never have that format combination
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
Nov 5, 2013
I have a listbox that lists some items from a spreadsheet, but what I cannot figure out is how to have the font color of the items be RED if the value if negative, and default BLACK when positive...
how to alter the code to accommodate this?
VB:
With UserForm1.ListBox4
.ColumnCount = 5
.ColumnWidths = "2 cm;3 cm;3 cm; 3 cm; 3 cm" [code].....
View 6 Replies
View Related
Aug 21, 2014
Formula which will count unique values in column A based on condition in Column B which "y" .....
Show ranges as A:A instead of A1:A100 as I dont know the size of the table, it can be thousands rows .....
View 3 Replies
View Related
Mar 10, 2014
I am working on creating a simple Excel Database of an Engineering Tool Room Inventory for work. However I can not seem to get one particular 'NESTED IF Function' to do what I want it to do. (In advance warning, I may be going down the wrong route and might have used the wrong Formula in Excel but from my understanding the 'IF' function seemed the most plausible.
Issue: All I would like to do is for a NESTED IF Function to be able to say this:
1. When a user inputs data into one column, for example 'Column A', with the following data: 4" Square
2. Then the neighbouring column, for example 'Column B', would have a NESTED IF Function.
3. Which would output a logical response, "A, B, C, D, E, F etc...", depending on what is written in Column A.
If 'Column A' - 4" Square, Then 'Column B' - A
If 'Column A' - 5" Square, Then 'Column B' - B
& So on...
But the NESTED IF Function should only put a logical response if there is certain values within the cell, for example:
User types a value into 'Column A' which contain - 4 & S, Then 'Column B' - A
User types a value into 'Column B' which contain - 5 & S, Then 'Column B' - B
& So on...
Currently to do the above function, I have utilised the following Excel NESTED IF Function:
[Code] .....
& The following happens:
1. User inputs data into 'Column A'
2. The NESTED IF Function then provides an automatic response.
3. However it relies on the data being inputted without the ' " (Quotation Symbol)'
4. & With me defining the exact phrase that should be typed into the cell, for example, "4" Square"
Other: I have attached an example worksheet of the NESTED IF Function in practice, with two tables. One showing what is happening with the code above and one table showing what I would like to happen but with no function.
Example - IF Function.xlsx
View 3 Replies
View Related
May 10, 2013
Date Due
Date Ressolved
5/16/2013
5/17/2013
5/16/2013
5/9/2013
5/9/2013
How do I make this change, If the date resolved column has not data entry, but todays date is past the due date cloumn data entry change cell red and insert text to read "past date due"
View 6 Replies
View Related
