I know I can do this by nesting a load of if statements but I was wondering if there is an easier way.
If in cell A2 I have the value 12 and I want cell A1 to return a value FOUND if any cell from A6 to A24 has the value 12 in it.
I need to pull data from Column C by looking at Column A. However it's not as simple as that. Under Column A, there are various fields, the first number represents a type of product and under what year it is currently in. So "300100" would represent a 3 yr product that is in it's third year, "210000" would be a 2 yr product in it's first year and so on. The problem is that when I pull data from the DB, some 3 yr products are flagged as products that are in their 4th or 5th years, which isn't possible. So I created an IF statement that takes this into account and ignores illogical combinations.
=If(A2="300100",C2,If(A2="301000",C2,If(A2="310000",C2,If(A2="210000",C2,If(A2="201000",C2,If(A2="510000",C2,If(A2="501000",C2,If(A2="500100",C2,))))))))
As you can see, I don't have the conditions "500010" and "500001" because it won't fit into the IF statement. Is there another way to go about doing this task? I've searched up the forums and someone recommended using the IF statement with VLOOKUP but I don't think it works in this particular case since I have 10 conditions.
I am trying to compare a single cell's Value in 1 worksheet to try to find a match in another column (or a range of cells). If I find a match I want the result to be a 1 and if it is not found then a 0.
Is this the Lookup function?
A have a table like this:
X 1 2
A 9 5
B 8 4
I would like to have a condition that when is equal to B and 2, return the corresponding paragraph in the matrix (in this case 4)
If lookup doesn't find a match in the first column, can I get it to return an
"ERR" or "0", something that would let me know there was no match? Right now
it returns the value in the second column of the closest value.
In cell b2 I have a formula =text(today(),"yyyy") which daisplays todays date as a year, in cell b3 I have =text(today(),"mmmm") which displays todays date current month.
I have a column of Months and Years
January 2014
February 2014 etc
I need todays (current) month and year to indicate in a helper column next to the relevent month and year as a number 1 to use as a reference to return date from the row where the 1 is displayed, I have tried =if(and(b2=a10,b3=b10),1,"")) and it dosent like the fact that the year in the colmuns is entered as standard text, is there any whay to do this?
I need to create a macro that will loop through a list of names on Sheet 1 / Column A, and if that name is listed on Sheet 2 / Column A, then it will set Sheet 1 Column B (for that one name) to "1".
The problem I've run in to is that both the lists change in length each time I have to run this (so no set range).
I've been able to get this to work with two ( nested) loops, where the first loop goes through the Sheet 1 list and the nested Loop looks for the name on Sheet2, but it is extremely slow.
Is there any simple VBA code or formula that I'm missing that could do this? I've tried to do something with the " Find" VBA command, but I can't see how it would apply to this if the list changes in size.
I am struggling with a formula, that will display a certain cell only if a match is found, else produce a sum. It will be easier to look inside the document rather than explaining it on here. Described as well as I can inside the document
(None of the data used is factual. I created it myself to replicate)
Overtime for Payroll.xls
I currently have the below formula to search for text of interest (A1) within each cell of a column (B6:B30).
=IF(ISERROR(MATCH(A1,$B$6:$B$30,0)),"NO","YES")
The formula works perfectly if there is only a single value in the cells. But not so well when the cell has multiple values separated by a comma.
For example:
Column B
X
Y
Z
T, X, Y, Z
It would return a "Yes" for X, Y, and Z because those are found individually. However, T will appear as a "No", because it is found within a cell that is separated by commas.
I want to return a result IF a certain value is found in another cell. I've attached a sample workbook, but here's a description:
On the "Inventory" worksheet, in column H, I want to lookup the quantity in column B from the "Warehouse Response" worksheet. If the Part # in column E of the response sheet is NI1088106, I want THAT value to show under the NI1088106 part number, and I want the quantities for the NI1089106 to show under that part number. Ideally, the result of the formula would show a "0" if no data is found.
Here's the formula I tried beside the NI1088106 data, but it's not giving the desired results: =IF('Warehouse Response'!E:E="NI1088106", VLOOKUP(LEFT(A3,3),data,2,0))
I have long text values that include names in cells A1:A100.
In cell D1:D10, I have a list of names that I would like to check if any of them is found in each of the cells in A1:A100 and if yes, return TRUE in column B.
So in a way, what I need is a a bit like the SEARCH function, only that I need to find multiple FIND_TEXT values. If just one of the names in D1:D10 is found, the formula should return TRUE.
The formula has to include a range across each row (for each student). The formula has to search for specific text contained in the classnames, and then return the result along the same row.
Below is an example, which I hope saves OK in the thread. If not I'll upload an Excel file:
A B C D Results:-
1 class1 class2 class3 class4 Gg Hi Fr Sp
2 10m/Gg1 10m/Hi2 10m/Fr1 10m/En1 TRUE TRUE TRUE FALSE
3 10n/Hi1 10n/En2 10n/Sp1 10n/Ma1 FALSE TRUE FALSE TRUE
Looking to find 1 of 2 words in a cell in column B and return the word found in the same row in column E. This seemed easy but I am not having any luck.
the cells in column B have several words in them but I am looking for 2 specific words "PLAT" and "ORIG". If the word is not in the cell, it should show a blank cell in column E in the same row, otherwise one of the 2 words should be in that row in column E. A VBA loop would be ideal but a formula that can do it might work as well.
I have a file which has in excess of 12,000 rows of data in 5 columns (sample file attached with fake data). The five rows are:
"First Name" "Last Name" "Name" (uses CONCATENATE to combine column A & B) "Email" "Date Attended"
There will be duplications in the list as people will have attended more than once over the years.
What I want to do is search through the email addresses (Column D with D1 being the header) and where there is a duplicate email address copy the cell to the right of the duplicate (F#) into the next available cell to the right of the first occurance and then delete the row with the duplicated email address.
I am on Windows 7 and Excel 2010
So I'm trying to merge some of the information found within two separate worksheets. I'm working with a list of ID#s that may or may not be found on both worksheets, and in a column on Worksheet1 I want to put some of the information found for the corresponding ID# on Worksheet2.
Now I've figured out how to check if an ID# on Worksheet1 exists within a column in Worksheet2 using the formula:
IF(COUNTIF('WORKSHEET2'!$A:$A,A2)0,"MATCH","NO MATCH")
(Excuse the syntax errors if they exist, I'm doing this off the top of my head for expediency)
However, instead of returning the word "Match" if it does indeed exist on Worksheet2, I would like to return the value found in Column E, on the row of the matched ID#, in Worksheet2.
i want to match a cell data with a range of cells and if matches return the cell reference in another cell
View 3 Replies View RelatedI have a condundrum that having trouble solving hoping someone may be able to help...
I am trying to return the contents of all cells that match a value based on another cell.. I have managed to achieve with the forumla below but it only returns the first value matched.
what I wish to do is return all values in ROW E where the value in ROW B equals Deliver and combine into one cell [F1].
ie.
B E F
DELIVER VALUEW VALUEW VALUEY
COLLECT VALUEX
DELIVER VALUEY
The formula I have currently uses index but only returns the first value found. I need to find all values and place in one cell. C5 holds value 'deliver'
=IF(ISERROR(INDEX( 'Salis Today'!$E:$E,MATCH(C5,'Salis Today'!$B:$B,0))),"",INDEX('Salis Today'!$E:$E,MATCH(C5,'Salis Today'!$B:$B,0)))
I have a product list and the products have different levels. I want to lookup and return the the rows with level C and return the article number and price. If the row has level A or B I want to skip to the next level C.
I want to use a formula or array formula and not a Macro.
Data
Article
Price
Level
KL507
56
A
[Code] .......
Expected result in other table
Article
Price
JK879
98
FJ893
98
RT344
23
I have a column of data in column and a column of data in column F. The data in column F is exactly the same as column C but in a different order. Is there a way to match column C to Column F and return the matching cell references for column C & F in column G & H? i.e. If the word Help appears in C27 and F40, match the two words and return C27 in G27 AND F40 IN H27. All of the values are unique (I hope).
View 2 Replies View RelatedI have a spreadsheet with 2 different sheets (DB and Workings). What i am trying to do is using VBA code is, need to match the state name CT from workings sheets (cell C3) with DB sheet of column B4 to B9 and return the Team values from column A4 to A9 in the row 4 (C4 to E4) of the Workings tabs (highlighted in Red) and similarly for the state name LA i want this process to get repeated.
View 4 Replies View RelatedI have an array formula which is working as expected returning the value of column K (offset by -1) if a number of criteria are matched.
[Code].....
The issue I'm having is there are occasionally duplicate values in column W and it's using the first value in its return, which isn't always correct.
Unfortunately due to the limited matches available the only way I can think to identify the correct value is by adding an additional criteria, which is that the correct value would always have a negative in Column X one cell above and to the right of where the value is being retrieved from. If there was no negative it would ignore this result.
Is there any way to add an additional criteria to the formula to stipulate that it must match a negative in column X but one cell above?
make an adjustment to my Formula so that if the Index match locates a cell that is blank it returns a blank cell rather than a 0. I know we need to add in =IF(iserror( but i am aving trouble..
View 5 Replies View RelatedI have a value in $ED$1. This is Value X.
Row A = $DO$7:$DV$7
Row B = $DO$10:$DV$10
[Formula location = $EE$1]
Where Value X occurs in Row A, l want to return the parallel cell in Row B.
Both rows are sorted in numerical order. There is a slight complication though: Row A might contain multiple occurrences of Value X. If Value X is repeated several times in Row A, I want to find the left-most occurrence of Value X in Row A, i.e. the one closer to Column DO.
I get a text file daily for routed deliveries containing a barcode, name, address and phone number that I convert into csv and then upload it into a route optimization software. after optimization is complete it has assigned packages to drivers and I export it to excel and print. My sorters are having a difficult time reading the addresses on the packages and assigning them to the proper driver, so I am trying to come up with a way.
My idea is that I can import the barcode as a custom field into the route optimizer and then when I export it, that barcode field will still be associated to that order. I want to scan the barcodes of the package into column C which will reference all of the barcodes in B and when it finds the match, it will return the value in A, the driver assigned to that particular package. This way my sorter will just have to scan the packages and throw it into the proper bag for the driver instead of having to visually scan the printed manifest to match.
When an exact match to the lookup value is not found, the function goes to the highest value that is less than the lookup value.
What do I do if I want to return the closest value, but not less than the lookup value?
For exampe, in my screenshot, if my lookup value is 6 horsepower, I want to return the efficiency for the next highest value (if no exact match) for 7.5 HP. Function would return 89.5
VLOOKUP.jpg
I am using a For Each statement to loop through a range to look for the match to a predefined variable. I have that part successfully set up.
But - I was it to STOP looping once it finds the first match.
Can I do this with a For Each statement? If so, how? if not, how to loop until a match is found?
I'm having problems with a formula. Here is what I would like to do:
I have a named range named "J46DATA".
If B5 returns "N/A" when matching to "J46DATA" and S5<>0 then return "DELETE" or IF B5 returns "N/A when matching to "J46DATA" and S5 = 0 then return " ALERT". anthing else can return false.
I started of with
=IF(AND(ISNA(MATCH(B5,J46DATA,0))
But it wouldn't let me put the S5<>0 after that lat parantheses.
I have two files. They both have US census tract numbers in a column GEOID2010. One also has a CSA2010 text description associated with each census tract.
I need to match the cell with a census tract in the first file (example1).xlsx with the census tracts in the second file (example2.xlsx), and then return the CSA2010 text from the next column over to the first file.
I've attached two example files.
I have a rather large table that I need to return the column heading from.
Within the table I have a list of Names in the left most Column (A) and a list of Percentiles in the header row (row 2) (incrementing by 5%). By inputting the name and a cell value I am trying to return the column header.
Using an example;
5% 10% 15%
Bob 3.5 4.1 4.15
John 3.6 4.2 4.30
Chris 3.3 4.9 4.95
Sammy 3.7 4.6 4.7
Like I said, knowing the name and the cell value, I want to return the percentage value. So for example, Bob would be one input value, and another input value would be 4.1. I would want excel to return 10%.
I have found different suggestions using a combination of index/match but even using these functions I cannot get it to return the COLUMN value instead of a cell value.
Working on an export from an e-commerce application that lists the item purchased, unfortunately not by item #, but rather a lengthy description. I'm trying to construct an item number based on that description which contains several constants.
Each item ordered appears in a single row, comprised of ORDERID, QTY, PRODUCT DESCRIPTION
I have several tables to pick out PRODUCT TYPE, COLOR, SIZE, etc. I need a formula to search to see which of the unique values appears in each in the cell and return the shortcut of that value.
PRODUCT DESCRIPTION example "Sweater - Blue - Large - Wool"
TYPE TABLE:
Sweater SWTR
T-Shirt TSHT
Jeans JEANS
COLOR TABLE:
Blue BL
Green GN
Purple PL
Black BK
I would have separate tables for each part of the description.
The formula I'm seeking would search the PRODUCT DESCRIPTION cell for the range of values for each 'part'. So it would search to see which of Sweater, T-Shirt, or Jeans was in the cell and then return the one that was - SWTR in this example. I would modify this formula for each 'part', in its own cell, and then use an =cell#&"-"&cell#&etc... to combine these returned values.
I know I can write a large if- chain using ISERROR & FIND, though some of these lists will get lengthy.