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)

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.

I have a work sheet called 'Cross Ref data' which I want to put a keyword in column A (row 1,2,3,4,5,6,7,8 etc)

I then want to be able to run a macro that will grab the contents of A1 in WS called 'Cross Ref data' and check if it appears in column D and E in WS 'Results'

If there is a match then for the entire line within WS 'Results' be copied to a WS that already exists called "Cross Ref Results" (please see the below heading '88888***********8888' for the layout) .........

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 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

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?

How 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 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 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

Modifying the following line by replacing the row number in AC2 and Q2 by an incremental variable? I mean something like “AC”&Rw , “Q”&Rw, &Rw, Rw being my incremental variable used to go through the rows.

I'm trying to create an overview which shows a breakdown of the total minutes for each task on each given day.

I need to get a formula to work in the grey area which sums the total minutes (column C) based on matching two criteras of task (column A) and day (column b).

We have a table that records payment dates and amounts ... and it categorizes how the payment was made. See attached file to understand the process.

Column A records the payment date. Two or more payments can be recorded for the same date!!!

Column B records the amount of payment.

Column C records the method of payment (OL or LB or CC or CK).

Here's what we need:

On a given date (cell J4), we need to match any dates in Col A ... and we need to SUM all payments that match that date .... and the SUM needs to be based upon the method of payment (OL or LB ...) and values stored respectively in E4,F4,G4,H4 (or left blank if no date match).

I'm trying to have a cell where I return the minimum value of column A but only when column B equals 380 and when C equals 80. I know how to do it if I select my range manually to be the range including all the 380's and 80's but I'd like to select the entire data set to look through so that I can easily change the criteria to say finding the minimum in Column A when B equals 420 and C equals 40.

I have numbers in columns a through cc and cannot put them in descending order. I am using this formula:

=match(CF1,A10:A1008,0) I have to use a new cell and type the same formula =match(CF1,B10:B1008,0) then again =match(CF1,C10:C1008,0) I AM RUNNING OUT OF ROOM! Is there anyway to search all columns =match(CF1,A10:CC1008,0) ?? This formula doesn't work when I type it in.

I have tried shift control enter for an array and still no go.

I have tried vlookup I have tried Index I have tried Iferror I have tried isna

I have attached a sample from aspeadsheet I am working on. The option reply sheet has '1' entered if the student has opted to take this course. On the other subject sheets I would like the names who have opted for that subject to appear. Stuidents can opted for multiple subjects. I have tried Match and Index functions but it is difficult as students can opt for more than one.

I have 2 columns with data in them, basically representing a gaussian distribution. Column A has the "X-axis" values and so is uniformly ascending with no duplicates. Column B has the "Y-axis" values and increases up to a maximum and then decreases again (this is data from an instrument and so its not completely smooth but is close). An example is below.

0 4 1 8 2 16 3 27 4 50 5 27 6 16 7 8 8 4

What I would like to do is get the 2 Column A values where the corresponding column B value is half of the max (in the case above, 25 is not available so the closest is 27). I am trying to calculate the difference between these values, so in the example, I would have 5-3. Is there a way to do this?

I have a spreadsheet where I want to match the PO info with the invoice info. I want to match the cost center, location, PO number, item number and the price.

Period Cost Center Account Item No PO No Amount Test

As you can see in that the formula is showing the first 4 rows with a difference even though that the difference is in the first two. How can i fix this?

My second question, on the lines with the 989.04 difference, how can I make the same formula to say "Fix the account" or to fix the column with discrepancy

I have been asked to analyse the contribution / performance of each student in a team. What i am trying to do is to count the number of times each student has work together and then try to work out their contribution. Got stuck on the 2nd bit for like 3 weeks flat. have been trying but not luck... seems to give me 1 or 0. so i assume my attempt is only a true or false answer! All i need is a count of how many times each student work with each other, and the best backbone formula is "count". This situation is best explained by my example, D15 is the closest i got

I'm working on a travel form, which allows people to enter a travel itinerary. From that, I want to be able to extract the cities where they are spending one or more nights - so that I can then do the calculations for accommodation allowances.

I am able to calculate the number of nights stay in each city where there is an overnight stop. But I'm stuck on how to extract every combination of City and Nights where Nights is greater than zero - there is no need to calculate for cities with no overnight stay.

I have attached an example spreadsheet.

Some notes:

- the itinerary and accommodation tables have to remain separate, as they hold more data than in the example, - the itinerary table can't be sorted for number of nights. It has to be in chronological order for each segment!

Every lookup function I check out seems to demand a sorted table.

I'm working with 2 sheets. Both have the same headings, A1 is CustomerCode, C1 is JAN, D1 is FEB and so on. The CustomerCode column doesn't match exactly between the 2 sheets, because some of the customers from Sheet1 aren't listed on Sheet2. I need a formula to put on Sheet 3 that will Sum the values in JAN, FEB, etc from the first 2 sheets for matching customers, and also bring over the totals from the customers on Sheet 1 that don't have a match on Sheet 2.

I have attached an example of how the finished product would look in my case. But also, here's this:

Sheet1 Charges

CustomerCode JAN FEB ABC 30 25 AEF 20 15 BBB 50 30 CED 15 20

New customers are added often, so I have a SQL Query that I can put in column A of sheet3 that will pull the same CustomerCode list as sheet1, so getting the names to sheet3 shouldn't be an issue.

I want to count all instances if the following conditions are true. In quotations, are the names that I am using for column ranges. Here are my conditions, I want to count the rows that have the following conditions.

When "dates" or J2:J25 is less than or equal to today's date AND "HTeam" or W2:W25 is equal to Civil AND "Percent" or K2:K25 is equal to 100