In the attached sheet, I want to look up values from table (A1:G5) and then for each occurrence of a particular number, return the value in the same row in column H, and sum when there are multiple values (see green shaded area for desired output. For example, the number 8 occurs in E3, E4, and G5. I want to return the sum of the corresponding values in column H (1,5, and 2). I've spent hours trying to work this one out, I'm sure this is probably an array formula, but I can't manage to make it work.
I know something very similar was posted before, but unfortunately, it wasn't what I was looking for. I have a Worksheet tracking several associates and the department they have done work in. (Our associates are contractors for other departments). I need to have a final worksheet that allows me to pull up a name, and display every dept they worked with in the past week.
I have a list of ID Numbers and Dates. Let's call this "List 1".
I also have a second list "List 2" which also contains a list of ID Numbers and Dates as well as a third column for Rating.
I am trying to extract the Rating for each ID Number from List 2 and display in List 1. The problem is that the Rating can change with time, so List 2 contains several different Date and Rating values for the same ID Number.
What I need to do is lookup the ID Number from List 1 and return from List 2, for that ID Number, the Rating at the closest date prior to the date for that ID Number in List 1. Example below:
I am remodeling my home and have put together a budget template. I have uploaded the file to make things easy to understand. Basically I want to return the values from column F and G on sheet "ledger" to the corresponding account name to column H in sheet "budget". For example, rows 14, 21, and 22 in the "budget" sheet currently correspond to the "fuel" account. I would like those values in column G (or F) to be added and calculated in cell H58 in the "budget" sheet.
I need to populate the Rep Names looking up 3 Criteria (Client Id,Curr Cov Id,Dom Buy Grp Id) from the Table 1, either one matches the Rep Names has to populate.
The data has to be pasted on WIP_Sheet in different columns.
Is there a way to use a lookup formula to return all values of a given text, not just the value of the first listing of the text. In an attempt to clarify my already confusing request, if A5 on "worksheet1" is "Land" I want E5 on "worksheet1" to return the summation of values assigned to "Land" "worksheet2" "column B" any time "Land" appears on "Worksheet2" "Column A".
I have this formula but it only gets the 1st Reuse code, I want it to get ALL reuse codes for that item.
{=INDEX('Reuse list '!$P$2:$P$24489,SMALL(IF('Reuse list '!$E$2:$E$24489='Status Sheet'!A11,ROW('Reuse list '!$E$2:$E$24489)-ROW('Reuse list '!$E$2)+1),1))}
I am wanting to use a vlookup or perhaps an array(?) to bring through multiple cell values based on being the maximum value. So say there are multiple employees (all with unique ids) under one manager (also with unique ID), I want to find the employee with the highest value in a particular column and return that employees details, such as name, employee number and the value itself, which are all in the same row. I have attached a basic example of the data involved.
the formular that i have in the attached file is returing all the customer numbers Is it possible to return only one value of each the data is in the "data" sheet and the formulars are in sheet1
I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.
I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:
To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).
I already have it working for returning a single value with the following formula but need it to return multiple values.
=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")
I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.
I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:
To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).
I already have it working for returning a single value with the following formula but need it to return multiple values.
=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10, MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP (INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")
Use a referenced lookup value from sheet "A", to return values, from several columns in sheet "B"
Things to note:
a) The lookup values sometimes repeat. I need all the associated values with each repetition as well.
b) The lookup values in sheet "A" are a comprehensive list, sheet "B" also contains some of these values but not all. Essentially, what I need to do is find a way to lookup each value in an account numbers column in sheet "A", against a different account numbers column in sheet "B".
If that value occurs in sheet "B" I want it to return the values from Columns X, Y, Z, (I want these values returned in sheet "A".
If that value does not occur in sheet B, the corresponding cells should remain blank.
If the lookup value occurs multiple times, I need all the corresponding values from each of X, Y, Z columns.
I need to lookup data based on a cells content. The answer has to output to a table as a sequential list. I have attached a sample workbook (there will be 100+ sheets in the final version!). The Register sheet lists competencies against page# and title. For each Evidence Sheet I need to populate the Competency grid (C4:D10) in a sequential manner i.e. no blank lines,and the Title (B13) based on the Page # (D2). I have looked at a great number of postings, but cannot see a solution that fits.
I can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....
My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)
I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.
I have a worksheet containing a list of states in column A and a list of companies in column B (along additional data in columns C-L). I want create a lookup formula on a separate worksheet where users can select the state from a dropdown menu and it will return the information from columns B-L for that state.
For example:
A B C D Alabama ABC Company Active Expires December 2009 Alabama 123 Company Expired Expired April 2008 Alabama XYZ Company Active Expires August 2009 Alabama Larry Company Expired Expired May 2006
How can I do this without losing my mind? I've tried various Index, Small and Match formulas and none of them work.
I have two columns of data, one containing Groups, another containing Items. Like this:
Group | Item A | Apple A | Orange B | Pear B | Banana C | Tire D | Coffee
etc.
On another sheet, I have each of the groups listed as column headings. I want to resort the data and display it in columns instead of in a single list.
I can write a VLOOKUP to search for "B" for example, and return "Pear", but I want to return all matches for "B" not just the first one.
Basically I want to return an array (to pass to another function) containing all data that matches my search. E.g. I want to retrieve an array of the data that matches 2013-09-20. Is the only way to do this a VBA func?
I need a look-up function that can return a value from an array that can be 3 to 7 rows in height. I want the function to always look at the last row in the array.
This is how it looks:
Column A Column B blank row 1000 A B C SUM 15 blank row
In this case I want the function to return 15 if I search for 1000. The problem is that there are not always a fixed number of rows beneath the searched value.
At work I have a spread sheet that I used to track material shortages by part number. So in column A of the spread sheet there is a list of part numbers that have shortages, column E contains a list of all sales orders that are affected by the shortage separated by a comma. I am trying to setup a query sheet where I input a sales order and get back a list of parts that are short for that sales order(basically reversing the original list to be by sales order instead of part number). The number of values in column E varies, sometimes a cell will have 1 value, sometimes 20+ and anywhere in between.
Example Sheet: A B C D E
123
012
234
789, 567
465
789
890
012
I'm already got a INDEX/MATCH that would show both shortages for sales order 012. But I can not figure out how to get the shortages for 789 or 567.
I would like to write a formula to return a value from a column that relates to an array within which my lookup value exists. See the tables below:
ID V1 V2 V3
105 27 3 149
[Code] .......
The tables above are on different sheets within a workbook. I would like to write a formula that returns the "ID" numbers from column "A" in the first table, based on values in columns B-D in that first table, into column "C" in the second table. For example, in this case, the ID number that corresponds with the value "12491" would be "109", since 12491 corresponds with 109 in the first table.
FYI, the "Value" numbers in the second table are calculated based on their rank (high-low) within the matrix in the first table.
I tried the INDEX-MATCH function, but it doesn't seem to work if I'm trying to find a value within a 2-D array- it only works if I'm looking in a single column.
I have a problem with the formula that lookup all values in ascending order and returning all the corresponding values. eg: I was intended to lookup for the value in ascending order under the Total Occurrence and returning all the corresponding value under the Nos Group but encountered the same Nos Group was returned when there is same value appeared under the Total Occurrence.
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
I'm trying to define a range of values in two cells M1:M2; lookup that range of values in a three column array (K12:M600) and sum the values in column M12:M600 if they are positive values. I've been trying this formula:
I have a spreadsheet of information (product codes and values) and want to use VBA to find the top ten list of products by value. The list of products is variable in length so I can pass the data to a two-dimensioned array.
How do I ask VBA to return the top ten products by value - there can be duplicate values so I just want ten (if there are 4 #9s, I just want two of them, any two)?
I have a problem with the formula that look up multiple records with the same values and return multiple corresponding values in ascending order. I am using Excel 2003 and it is a bit complicated to explain so I have attached a sample spreadsheet to show what I mean.
What I want was after I have sorted the occurrence value in column E based on column B and I want to correspond the Rank in column D based on column A in ascending order for the same occurrence value in column E.
Eg: There is two occurrences for number 1 at E3 and E4, and three occurrences for number 2 at E5, E6 and E7 in column E. Then the Rank for the first occurrence for number 1 in D3 should be ranking 6 and the second occurrence for number 1 in D4 should be ranking 7, so the Rank for the first occurrence for number 2 in D5 should be ranking 3, D6 should be ranking 4 and D7 should be ranking 9 based on column A and B, etc.
Hi there, please can you help me transfer data from 1 spreadsheet to another (I have attached a file which has 2 sheets).
I am trying to populate columns G to L (sheet B) with data from column H (sheet A) - the lookup is between 'Material' & '7410','7090','7680','7060','8630','7580' on sheet B & material & plant on sheet A.
I have tried H&VLOOKUP & INDEX/MATCH but I keep returning a #N/A..
