IF Statements With VLOOKUP: If Column "I" Equals "Min", Then Look At Column "E"
Sep 10, 2009
If Column "I" equals "Min", then look at Column "E"; if value in Column "E" begins with "88216" then input "Thuraya" in Column "G". If Column "I" equals "Min", then look at Column "E"; if value in Column "E" DOES NOT begin with "88216" then input "Voice/Sat" in Column "G". If Column "I" equals "Msg", input "SMS" in Column "G".
I need for all of this to be in one "simple" (haha) formula. I have tried IF/THEN statements, VLOOKUP's, etc, but cannot for the life of me figure this one out. At that point, Column "J" will VLOOKUP the correct rate and Column "K" will total the cost for the Row.
View 3 Replies
ADVERTISEMENT
Mar 5, 2014
I am trying to count the distinct times a value shows up in a column, if another column has a 2 in it.
For example:
Columns
A, B
2,P25
2,P25
3,P5
3,P6
2,P5
The results shoud be: (2) Because I only want to count the P25 one time.
I'm using Excel 2010
View 2 Replies
View Related
Jul 25, 2014
how to count the unique occurrences in column A based on the value in column B. In the example below, I am trying to count the number of unique names in each state (Illinois=1, Colorado=2, New York=2). I would like the formula to count unique occurrences for the entire column A because I will be adding to the list.
..A.............B............C
John......Illinois
John.....Illinois
John.....Colorado
Alex.....Colorado
Alex.....New York
Steve...New York
Steve...New York
View 10 Replies
View Related
Mar 25, 2014
formula that would count the instances in Column C, where Column A contains "LH" or "RH" as part of the word, and column B equals "Either".
View 5 Replies
View Related
Nov 14, 2009
I have two columns, Column A which is a list of numbers and Column B which is a list of different sports. What i want is a formula that adds all the values in column A where the adjacent cell in column B equals a given sport (i will use Golf in this example).
View 2 Replies
View Related
Mar 13, 2012
I am looking to insert a row every time any cell in column A = R
This will happen around 50 times.
The inserted row will need to have an R in column A and the number 349273 in column B.
View 4 Replies
View Related
Mar 22, 2012
This is an extract from my workbook.
11661681192203
I need a formula in column d that will return all the missing values between for example b1 and c2 and b21 and c21. The catch is that the numbers in column b and c can be anywhere but row b will always have the starting number and next to that cell the ending number in column d.
a further condition is that it must only calculate the missing number if the value in column a=1. if it is blank than nothing must be calculated.
View 1 Replies
View Related
Aug 6, 2012
HTML Code:
Sub DeleteEmptyColumns2()
Dim Cell As Range
For Each Cell In Range("4:4")
If Cell = "0" Then
Cell.EntireColumn.Delete
End If
Next Cell
End Sub
I am attempting to remove columns in my table of data, using the above code seems to remove some them but not all, is this the best way to loop through a range?
View 8 Replies
View Related
Mar 29, 2013
I've seen a few examples of macros to delete the row if the first letter is something, but not if the first 2 is equal to something.
I'm looking to go thru every row that contains data and look in Column B to see if it starts with P4 or P5. If that's true, then delete the entire row.
View 8 Replies
View Related
Feb 8, 2010
I want to get value from reference one column and return value by corresponding row from another column. This can be done by vlookup column. But it find value from 1st column and return value from given relative column. That mean find from Left column and return value from right side given column number.
But I want find from Given column number and return value from given left column. For Further detail see the Attached file.
View 3 Replies
View Related
Apr 6, 2007
=IF(ISNUMBER(VLOOKUP($E8,Su!$D:$K,7,FALSE)),VLOOKUP($E8,Su!$D:$K,7,FALSE),)*$I8
In the first vlookup it picks up a value. I need to do the same vlookup function but it should also pick up another value in column 9 and should add value in column 7 and 9 and return it. Employee id numbers are in column E in the first page.My formula checks for the employee id in Su sheet and retrieves the value from the seventh column. I need to retrieve also from 9 th column and add them together.
View 8 Replies
View Related
Jan 16, 2009
I've got a list of numbers between 0 and 1
eg.
0.25
0.59
0.73
0.15
0.99
in column A
if I want column B to state "low (0.00-0.33), medium (0.34-0.67) or high(0.68-1.00)" for a corresponding value in column A, how would I do that?
View 3 Replies
View Related
Oct 24, 2007
I am manually scrolling through Excel right now to find all blank cells in column L. Some cells have dates, others are blank. IF L is BLANK, I go to row K and copy/paste row K to row L. IF K is BLANK, I got to row I and copy/paste to row L.
Is there a statement I can put into row L to basically say "If blank copy/paste from row K. If row L and K are blank go to row I and copy/paste to L." ???
View 4 Replies
View Related
Aug 31, 2009
I have a table of some numbers, and along the left side is a column representing what each row of data is. So in this case, it is a list of letters. What I'm doing is in each column of the data, I find the maximum number, and then I want to have it list below that what letter that maximum number is from. Here is an example:
View 2 Replies
View Related
Apr 4, 2014
I want to look up a value in the third column of a table and return the value in the first column of that row on the same table. What do I do?
View 2 Replies
View Related
Feb 24, 2014
I have a table as below (you can also refer to the attachment) and need to do the vlookup with EE Code if the left 2 characters of "EE Code" = 91 to 95.
EE Codedeptif left 2 characters of EE code= 91 to 95, vlookup dept code
921600012345678
vlookup arraryA12345623456700
39914123 test785637039914123
38214125 test915580057889056
40964197 test932660038214125
77014155 test913580039914123
345699940964197
945800077014155
953790300123432
915800012345678
045699957889056
585637039914123
View 3 Replies
View Related
Sep 27, 2012
use the VLOOKUP function when the column I need to search is not column A?
I have a Workbook with two worksheets in it and in Sheet 1 I have 10 columns of various text and numbers. In cell A1 of sheet 2 I will fill with a certain code that appears in column E in Sheet 1. The result I would be looking for should come from Column A in Sheet 1. I thought that the following would work copied into B1 of Sheet 2 but it doesn't appear to - =VLOOKUP($A1,'Sheet 1'!E$1:$E$481,1,FALSE).
View 3 Replies
View Related
Jul 17, 2014
Currently, I have a standard vlookup forumla that looks like =Vlookup(B2,Sheet2!A:G,5,false)
However, the data on sheet two is ever expanding--each month, specifically. And I need to return the most recent (so, rightmost filled) column. Right now, the column is 5, but next month it will be 6 and so on and so forth.
View 7 Replies
View Related
Feb 12, 2009
The data:
There are a number of columns, but we'll use the first 2 first. Column A has the dates from 1/1/2009 to 12/31/2009. Column B will has numbers, mainly 0-100. One cell will have a 'report date' which is just a date.
The formula needed:
I have a few cells labeled wk1, wk2, wk3. Week 1 will start with the 'report' date. wk1 I would like the Average of Column B IF the dates in Column A are within the 'wk'. wk1 = report date TO report date + 6. wk2 = report date + 7 TO report date + 13 and so on.
My goal is to have the formulas all set so that when we move to another report date and the raw data is added, I can just change the 'report date' and everything (the wk numbers) will automatically update with the new raw data.
The furthest I got was this, but it didn't work.
=AVERAGE(IF('A3:A33>=VALUE(D3)
View 9 Replies
View Related
Mar 24, 2003
I would like to do a VLOOKUP (or similar operation) to grab information from a column based on a column name instead of column number.
For example, instead of:
=vlookup(a2,t5:z10,4,FALSE)
I would like the formula to read:
=vlookup(a2,t5:t10,"COLUMN_NAME",FALSE)
View 9 Replies
View Related
Oct 7, 2009
I need to fill in the third column on sheet 2 from the 3rd column in sheet 1. do I need an IF statement combined with vlookup??
sheet 1
mark l XYZ l 45
katy l ABC l 3
mark l ABC l 6
katy l XYZ l 45
mark l DEFl 4
sheet 2:
mark l XYZ l
katy l ABC l
mark l ABC l
katy l XYZ l
mark l DEF l
View 4 Replies
View Related
May 12, 2014
I'm using vlookups within IF statements to look in a number of various speadsheets depending on what the lookup value is.
So for example,
Formula:
This works well enough however I need some look up values to look in multiple spreadsheets as the data is split out between several spreadsheets. So for example, if B1=Hello I want to vlookup to 2 different spreadsheets.
I know I could do two instances of a vlookup within the formula, both for "hello" but looking to different spreadsheets. But doesn't this mean that if the first vlookup finds the value, the second one will overright it with an "#N/A" if it doesn't find it?
View 2 Replies
View Related
Dec 8, 2008
I am new to this thread and hope you can find the time to help me. I am using excel to look up data manually entered in Widgets, Gidgets, Lidgets fields to find an exact match in an array and return the value in "field 1" with no luck. My lookup formula should return "9" from the Field 1 column.
Data Entry:
Widgets3
Gidgets6
lidgets5 ........
View 14 Replies
View Related
Jan 27, 2006
I would like to use an if statement to fill in data in one column - I have 1
lookup table with 6 columns and a spreadsheet with multiple columns -
basically I want to ask if a cell=13 go to my look up table"res type"
A$2:$B$220 if not go to the same lookup table but go to E$2:$F$220
this is what I wrote but it is not working: ...
View 12 Replies
View Related
Oct 4, 2009
I have a simple v lookup =VLOOKUP(A2,$J$2:$L$990,3) now I want to add to this with an if statement. I don't know how to string them together. when I find a match from the lokup i want it to insert price if it is less than current price =IF(E5>L5,L5,0) L5 would be the same as 3 in my lookup. I would also like to insert the price if it is higher than my current price. if they match leave a 0
I am not sure if I am going about this right I am a novice here and trying to expand my skills.
View 9 Replies
View Related
Jul 14, 2009
Is it possible to do a vlookup from a worksheet where the unique identifier on the source sheet is in a column other than column A? I figure this question has been asked multiple times, but I wasn't able to find the answer.
View 6 Replies
View Related
Aug 19, 2014
I have attached a sample work sheet that has the directions and a sample.
I want to use vlookup (or any relevant formula) to find a value in a table if it falls between two dates. The sample worksheet does a lot better job explaining this.
For instance, if I want to find 3/1/15, it is between 2/1/15 and 4/1/15, so I want the dollar amount to be $8.75
Sample.xlsx
View 2 Replies
View Related
Jun 8, 2009
I've got a worksheet that works out levels for test papers. The students either take an 'A' paper or a 'B' paper. The info as to what paper they have take is in a cell, I have then tried to use an if statement to determine which paper they have taken and which vlookup chart to use. however I'm just getting the dreaded #value!
=IF(E15,"A",VLOOKUP(G15,'Vlookup tables'!I19:J24,2,)),IF(E15,"B", VLOOKUP(G15,'Vlookup tables'!I29:J34,2,))
View 9 Replies
View Related
Aug 12, 2008
i've got a bunch of vlookup statements in my worksheet and i now need to create if statements based on them. however, the if statements do not recognize the vlookuped values. how can i get the if statements to work based on what the cell SHOWS and not what the formula is? i realize i can copy and paste values but i would like to retain this worksheet as a template.
View 9 Replies
View Related
Jul 26, 2006
I am trying to return a text statement using nested IF statements. In order to find the value in the IF statements, I have to use lookups.
Example: ....
View 14 Replies
View Related
