Is it possible to do the following?
Worksheet1!A2 holds a text value
Worksheet1!B2 holds a text value
Worksheet2!D2 holds a text value
Worksheet2!C2 holds a text value
Is it possible to use vlookup to find a match for Worksheet1!B2 in Worksheet2!C2 where the combination of Worksheet1!A2, Worksheet1!B2 is the same as the combination of Worksheet2!D2, Worksheet2!C2?
I am trying to match the first few words in a spreadsheet with another column via vlookup. I tried [URL] ..... but it didn't work.
see attached spreadsheet. If column A and column N match up, I need a YES in column, otherwise a NO. I need formulas for the 4 first words matching, 5 words matching etc.
I have an output table that looks like the followings:......
There is 1 input for the spreadsheet: What month? Enter 9, 10, 11 etc. I need it to pull from a data sheet that is set up as such:......
So effectively it needs to vlookup the name in column A, (A, B, or C), and then it needs to match that to the column that has the same name as the output table (Crit.1 or Crit.2 and the number of the month above it (10,11,etc). I attached an excel sheet to better demonstrate
I tyring to match column A:B with column F:G and copy the value in column H to column C.
View 3 Replies View RelatedHow can i add a value suppose 10 which is in cell A2, now i use vlookup to search
lookup_value=A2(i.e.10),
Table_array=column c,
Column_index_number=1 and
range_lookup=0
so when vlookup finds the matching value the sum should be 10+10=20
also it should match -10, so that sum will be 0.
I am looking to automatically assign products to a relevant categories on my website and I am working on a spreadsheet. Each category is has a four digit number assigned to it and categories are limited to 3 (Primary, Secondary & Tipperary)
For example:
Computers - 1000 - Desktops - 1100 - Dual Core PC - 1101
Computers - 1000 - Desktops - 1100 - Quad Core PC - 1102
So as you can see you can tell a Dual Core PC is in the Computer & Desktop categories just by looking at the first two digits. The last two digits are the category number. 01, 02, 03 etc.
What I wish to do is associate keywords to the category determine which category that product should be assigned.
Product ID
Product Title
Product Description
Product Spec1
Product Spec2
1
HP Desktop PC
HP Desktop PC's are computers
Intel Dual Core Processor
500GB HDD
2
HP Desktop PC
HP Desktop PC's are computers
Intel Quad Core Processor
1TB HDD
Keywords assigned to category 1101 are Computers, Computer, PC, Desktop, Dual Core.
Keywords assigned to category 1102 are Computers, Computer, PC, Desktop, Quad Core.
And the spreadsheet looks like this:
The script/sum would look at all the cells and match the highest count of keywords to that particular category and populate additional fields called Cat1, Cat2 & Cat3. Therefore the final spreadsheet would look like this:
Product ID
Product Title
Product Description
Product Spec1
Product Spec2
Cat1
Cat2
Cat3
1
HP Desktop PC
HP Desktop PC's are computers
Intel Dual Core Processor
500GB HDD
1000
1100
1101
2
HP Desktop PC
HP Desktop PC's are computers
Intel Quad Core Processor
1TB HDD
1000
1100
1102
How this can be implemented? possibility with the use of VLOOKUP or MATCH commands?
I believe a vlookup would be fine but there's was only 1 value to match but as it's multiples it would need to prioritise based on the number of keywords matched and not just the first match found.
[URL] .......
I am doing a project where I have two lists, one list is much larger than the other but has more info. I need some of that info for my other list, so I have used vlookup, but it hasn't helped.
The formula that I am using is
=VLOOKUP(B2,[Report.htm]report1183662451726!$A:$B,2,FALSE)
now the value I am searching for is "ANOS" which I know is in the search column, but the answer when I search is #NA. When I take out false the right value will fill in fro "ANOS", but if two words are similar such as "ALLN" and "ALEX" then it will give the same values for both which isn't right.
I have a worksheet with 2 tabs. Tab 1 has data in two columns, column A contains a part number (6 characters long) and column B contains a value for each part number.
Tab 2 is a sheet with multiple colums of data. I need tab 2 to look at the data in tab 1 and display the value of each part number in a specific column that has corresponding part numbers but are 15 characters in length. The part numbers in tab 1 are the last 6 characters of the part numbers in tab 2.
I have been trying to use vlookup in tab 2 but it won't work because the column of part numbers in tab 2 is a full part number and the part numbers in tab 1 are only a partial part number (last 6 characters of part number in tab 2).
Tab 1 is a worksheet that is created from a Access query of a sql database. I cannot control the abbreviated part number that the query generates.
Tab 2 part numbers (the full 15 character number) also is generated from another access query which I also can't control.
Is there any way I can have tab 2 sheet look at the data in tab 1 and display the values for the part numbers in tab 2.
Is is possible to construct a vlookup to identify that there are duplicate values in the Lookup_value and the Table_array and return the second and third values in the Formula result?
In the acutal project the Lookup_values and Table_arrays are in separate worksheets, but I think the example below represents the issue I'm working with.
I'm using Excel 2010
ABCDEFG1=SUM(IF($A$2:$A$8=A2,1,0))=VLOOKUP(F2,$A$2:$B$8,2,FALSE)
2JOHN13BILL12BILL11BOB14MARY11FRED15JOHN23JOHN16FRED11JOHN17BOB11JOHN18JOHN33MARY1
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].....
I am attempting to place a formula in cell b3 in the attached spreadsheet on the "sheet to pull data into" tab 1 (cell is highlighted yellow) to retrieve value in the yellow highlighted cell in the "Sheet that contains data" which is on a seperate worksheet within the same workbook.
The cells highlighted red in the "sheet to pull into" tab (cells: a1, a3, b1, and b3) are the criteria that needs to be matched to those cells on "sheet that contains data" tab in order to ensure the value returned is correct.Sample of problem.xlsx <----see the attached sample spreadsheet.
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
I am new to Excel macros and programming, and am running into a problem that I can't figure out how to solve. I have read through the forum and tried various examples, but I am still not getting the result I am looking for.
I have a spreadsheet that has a variable number of cells in-between blank rows (the data can be 1 to 6 rows). I need to sum up the rows of two columns and see if they equal 0 or if there is a difference. If there is a difference, I want to make the cell red. The exact problem I am having is I can't make it check the # of rows and run the formula based on that number of rows. I am attaching an example of my raw data followed by an example of what I am looking for as the end result.
im looking for a formula that will find matching dates in column a and then i want to see if column b has a matching time in it
View 11 Replies View RelatedI have 2 worksheets - main and match. In the main I have something like the following:.....
There are 200 entries in the main work sheet. In the match work sheet I have something like:......
I need to be able to flag if the entry in the match work sheet is already in the main worksheet by using the unique keyID. This flag will be in the match worksheet.
I am using the following array formula: {=IF(A3='[main.xls]Main File'!$A$8:$A$16,"y","n")} and was hoping that this would give me a Y in my flag column but this only shows a N. If I just do a straight if statement then I get a yes: =IF(A2='[main.xls]Main File'!$A$29,"y","n")
I have a table which is 8 columns wide by approximately 1000 (+) rows. I am struggling with the following;
if I type in seven numbers somewhere I need to get returns on a full 8 cell match? (and possible partial, but exact matches to 4+out of 8 numbers) All the numbers in individual cells are no larger than 99 and (in theory) are arranged from left to right in ascending order.
Can anyone point me in the right direction?
I don't want to use auto filter as I am just looking for a reference that I am searching the correct number. Does that make sense?
I am struggling with a macro that I’m trying to create. Basically what it would do is it would start at cell K1 on a sheet called “Database” and look for the value contained in cell K1 in the entire sheet.
It would paste all matches until no more are found into a sheet called “Matches”. Cell K1 would also be moved to this sheet if matches were found against it. It would then move on to the next non-empty cell in column K.
If no matches were found it would move on to the next non-empty cell.
I have a list of unique numbers on sheet 'List'. On another sheet, 'Data', I have a columns of data and column E contains a number which will match a number in the list on sheet 'List'.
What I want to do but don't know how to is loop (i think) through the numbers in 'List' and every row in 'Data' that contains a number is moved so that the data is grouped according to the number. i.e all rows with 78878 will appear beside each other and so on.
Neec code that i could use that will automate this so the user can just press a button and the data is reorganized?
How do I write a nested COUNTIF formula ( assigning a value of 1 for each cell) for a range of cells that does NOT contain "", "OFF", or "REQ OFF" ?
For example:
A row of 20 cells (A1:A20), 5 are blank (""), 3 contain the text "OFF", 4 contain the text "REQ OFF", and the rest contain anything other than the three values just mentioned.
I need a sum number for all the cells in that row that contain any other value. In this example, the output value should be 8. Out of 20 cells in the row, a total of 12 cells had either "", OFF, REQ OFF, and the remaining 8 had some other value that did not equal those.
Some sort of COUNTIF(AND(... Can't seem to figure this one out.
I have four columns of info. Two are check #s and amounts from the bank and two are check #s and amounts from a database. How can I delete check #s(along with their amounts) that match? Is there any way to detect check numbers that match but amounts that don't?
View 5 Replies View RelatedI'm currently having a hard time creating a formula to verify if the contents of 2 cells match.
Example from Spreadsheet -
Column C: MATCH / NOT MATCH
Column D, Row 4: MSG
Column E, Row 4: SMSG
When I attempt to create a formula for Column C, it registers the "MSG" within "SMSG" and lists the result as "MATCH".
is it possible to provide a formula to match as per the uploaded file. Data in Sheet one columns A and B, compared to what is in Sheet two column A I have a lot of data, and only need a Y for yes, and N for No as a result.
View 3 Replies View RelatedI want to know if there is any formula to match two numbers which have same digits but are in different style. I have attached a spreadsheet with numbers in column A and Column B. Both columns have the same number just the style is different, is it possible that i get a match based on the digits match and then return the output as correct or wrong?
If all digits match from column A with column B it should show as correct else wrong.
We have a report with 2 different sheets. On sheet one there are 13,000 rows of data and columns A-H are filled with different data. column A is our item id and column G has a date that states when the item was sold last. Well, the date in column G is wrong for about 7800 rows and needs changed.
The second sheet in the file is the 7800 rows with just the item id in column A and the correct date in column B. We are looking for an easy way to replace the rows in the first sheet with the correct information from the rows in the second sheet without manually replacing it line by line.
I almost forgot to add in, that in both sheets, column A with the item ids are the same item ids on both those sheets. So column A on sheet one has 13000 rows and included in those 13000 are the 7800 item ids on sheet 2.
We tried using the VLookup function that is incorporated into excel but had absolutly no luck, mostly because we have never used this function before. Sheet one already has all the lines of sheet 2, its just that sheet one has the wrong dates for the item ids and sheet 2 has the correct dates. im trying to replace the dates on sheet one from the dates on sheet 2 matching on the item ids from both sheets......
I have a long master list of registered members, column C has last name, column D has join date.
Now I have a short list of last names with join dates.
I want to compare the short list with the master list to find names that are already there, by comparing the last name and join date.
I have a sheet containing a list of client name,the date they've been assigned, the type of account they have, the status of the account, and the last update date.
What I'm trying to accomplish is to extract the client name and the type of account on that table that shows as "complete" in status. However, it gets complicated since I need to separate the data that had been 'completed' under the same month, with those completed the other month.
For example, I have been assigned 2 clients this January. I closed one in the same month, and the other closed at February. The first client would be listed under the first table (Completed within the month) and the other would be on the second (Completed on previous months)
Since this would be monthly, I am thinking if it would be more efficient to include the table per sheet (as one sheet corresponds to a month), or consolidate everything to a single sheet... I'm not sure which one would be best.
I have a sheet that returns 4 numbers where 2 will match. Is there a command that will return that matching value?
For example:
cell 1 = 5
cell 2 = 9
cell 3 = 4
cell 4 = 9
something to return the value 9
i have a worksheet that is about 100rows x 100columns.
i would like to create some code to find a certain record. for example..
a1 a2 a3 a4
the record might be " tony tony alex tony"
basically, this group of cells could be any where in the group and i need some code to search the worksheet and then outline it. i just want to be able to find a certain block of cells and outline it.
I have 2 pretty large zip code lists, we will call them columns A and B. I know that I have some from A that match to zips in B, but I have alot more zips in B that don't match to column A. I need to identify all of the zips in B that do not match a zip in column A.
View 9 Replies View Related