I have a table of data with years in row 1, week # in column C. The data array is from C2:O34.
I want to find the max value in each year column, then look up that result and return which week that value occured.
Is there a vlookup that allows you to specify which column of the array has the lookup value and which has the lookup result?
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 spreadsheet that I would look up two criteria in the table array, which first is down the column and next cross the row.
It would be easier to explain the scenarios by referring to the attached file.
On one sheet (KPI) I have either the values "x" or "" in the range A84:A89 to mark wheter to use the corresponding project in the range B84:B89. On the sheet X-ref I have the same project names in range T4:Y4 and a corresponding target value in T8:Y8
What I want is the sum (or average) of the marked-projects target-values. The result should end up in KPI!G31.
In other words I want
=sumif(A84:A89;"x";'X-ref'!T4:Y4)
but it doesn't work since the first range is an column-array and the second range is an row-array.
I have a dropdown box to choose a category.
When the category is chosen it displays the relevant course names which corresponds to that category (this could be 1 course up to 10 different courses).
Along with that course name it gives the relevant course overview.
This works exactly how i want it to. (thanks to Zbor from a previous thread)
I now want to expand further and give the corresponding course objectives for each course that shows up in the results.
I have attached a workbook as its much easier to see the dropdown box working.
Sheet 2 is the working sheet and sheet 1 will contain all the source data.
I have highlighted the columns affected in Blue Blue column on sheet 1 will have the course objectives on the same line as the course they belong to Blue column on sheet 2 is where they will be displayed when the correct category selection is made.
The course objectives are unique to the course title and course overview.
I have a tab that has 2 columns of data and I want to be able to return a value in column A if my data matches column B. If column B has the text TRUETRUE, I want to bring back the corresponding data in Column A. How do I return all the data in Column A for all the TRUETRUEs in column B? I can only get the first instance of TRUETRUE.
View 4 Replies View RelatedI have a sheet with Product IDs and amounts. A Product ID may occur more than once. I want to sum all amounts for all occurrences of each Product ID.
(see attached file)
I have written the following code, but as you can see, it is returning an error, so I've done something wrong. I'm also getting a Compile error: "For Each control variable must be Variant or Object"
Function GetTotal(PO_ProdID As Range, LURng As Range)
'
' Function to Lookup Concatenated PO # & ProdID
' & Return the Total of all dollar amount listed
'
Dim ce As String, tot As Long
tot = 0
For Each ce In LURng
tot = tot + ce.Offset(0, 1).Value + ce.Offset(0, 2).Value
Next ce
End Function
Assuming 1st row is a header row
Sheet1, Column A
1230000_XL07 - WB OPS
1230001_XL08 - WB OPS
1230002_XL09 - WB OPS
Sheet 2, Column A
1230000
How do I lookup 1230000 and return 1230000_XL -07 WB OPS in B2
I need to pull info to additional tabs. The problem I am having is that the data is sorted daily based upon performance numbers, so the REP names in column A can change based upon the data reported in column C.
What I want to accomplish:
Pull daily data for each unique name in column A to a team tab/worksheet that will be used to report that team's daily data.
Below is a representation of the data I need to break down by Rep Name and Team.
Rep DATA DATA DATA DATA DATA DATA DATA
1 2 3 4 5 6 7 REP 2 27.5% 14
51 86 REP 1 33.3% 14
42 74 REP 3 19.3% 11
57 86 REP 5 19.6% 9
46 66 REP 4 33.3% 9 ...........................
I have a workbook that I use to calculate sales by agents. In the workbook I have the following sheets:
Monthly_Report - Where the results are return to the from end user
team_ref_sheet - Where team / manager details are added / updated
data_sheet - Where my raw data is added
On my team_ref_sheet I have created 4 columns that have been renamed using name manager as follows:
team_manager
agent_names
dealer_code
employee_number
On my "Monthly_Report" sheet I have created a table where I want m data to be displayed to the front end user. The table starts in column C8.
In column D10 through to all the other rows I would like my sheet to reference to my "team_ref_sheet" and return all agent names in the range, up until it reaches a blank row.
After a lot of searching and trying I still didn't get to something that works.
My situation:
sheet1
column A column B
true "abc"
true "bcd"
false "cde"
true "def"
false "efg"
false "fgh"
on sheet2, in cell A1, I would like to get all results from column B that correspond 'TRUE' in that row.
So, in cell A1 on sheet2, i would like to see "abc", "bcd", "def".
VLOOKUP returns only one result, of course. How do I solve this?
I'm not good at VB, so preferably only with formulas!
I have a series of daily data in columns sorted by date (see attached .xls).
I am trying to create a table that extracts the value on the last day of a given month, and of the first day of that month, for each of the months in the data series.
I have been trying to get lookups and match functions to work but to no avail.
I'm trying to use this function which was posted as an answer (looking up a single value and returning multiple results, concatenating those results in one cell):
Function getfiles(DRng As Range, LURng As Range)
For Each ce In LURng
If ce.Value = DRng Then
holder = holder & ce.Offset(0, 1).Value & ", "
End If
Next ce
getfiles = Left(holder, Len(holder) - 2)
End Function
What is "ce" here? Auto Merged Post;additional info:
the original question was posted by jwhite68, Feb 27th 2007, "Return Multiple Values From Lookup To Single Cell"
i have this file that has about 12000 rows of numbers (a individual number can appear more than one). i'm trying to use a lookup to find if that number appears and if it does then bring back the amount next to the number.. however because the vlookup sees that the number more than one in the list it will bring back #N/A every time. Is there a way to get around this??
View 4 Replies View RelatedI need to process weather data that is collected either hourly or daily for several weeks - so for example I'd have Column A showing hourly intervals from 00.00 to 24.00 with Column B showing rainfall amount.
I need to output a single total for each day, say in Column C. I'm thinking I need to specify an array of 24 values from Column B, with a loop to repeat through until it runs out of data ?
I've started to piece together a subroutine but I need a grown-up to debug it ;
'Option Explicit
'routine to sum monthly data and output
Option Base 1
Sub monthdatasum()
[Code].....
If a player does not qualify, their name and score should not be included in the final results.
I currently have their Name missing, but the NUMBER is showing up.
I've explained more in the attachment.
INDEXARRAY.xlsx
I have 4000 rows of data with numbers in it (only one column...column C). These numbers are consecutive but these have breaks in between. So I am trying to find out where those breaks are and output those points in a list. So here is a macro that I came up with...but these array doesn't seem to write out results.... can anybody help please?
This macro tries to compare the two adjacent cells and if the difference between the values is greater than 1 then it copies that value in an array and writes that value in a cell in the same sheet.
Sub Macro1()
Dim temp(1, 1000) As Integer
i = 0
previouscell = 0
For Each cell In Range("C:C")
currentcell = cell.Value
abc = previouscell + 1
If currentcell <> abc Then
temp(1, i) = currentcell
i = i + 1
Range("D" & i).Value = temp(1, i)
End If
previouscell = cell.Value
Next cell
End Sub
I would like to use array v to store all the results of the for loop u...How can Ido it?
Dim myRange As Range
Dim AnsRange1 As Integer
Dim AnsRange As Range
Set myRange = Application.InputBox(Prompt:="Select row to insert 10 rows below", Type:=8)
AnsRange1 = myRange.Row
Dim u As Integer
Dim v As Integer
Dim var() As Single
v = 0
For u = 23 To 24022 Step 9
var(v) = u
Next u
If Not (AnsRange1 = v) Then
MsgBox AnsRange1
Else
Range(AnsRange1 & ":" & AnsRange1 + 9).Insert Shift:=xlDown
End If
End If
I have data in one column which is text based (A, B, C, D, E) and in the accompanying column is a response (1, 2, 3, 4, 5).
I want to be able to find those who answered 1 and then group to letters - so if 'A', 'D' and 'E' replied '1' then I want the result of the formula to be ADE.
I have been doing this manually and just typing the results in from a print-out but am hoping I can automate and get Excel to do the work for me to eliminate inputting errors!
I want to look up a particular value and return according results horizontally and vertically. Attached is the excel.
View 10 Replies View RelatedI have been using this formula to do lookup and return values
"=INDEX(ResultsColumn,SMALL(INDEX((CriteriaColumn=Criteria)*(ROW(CriteriaColumn)-ROW(FirstCellOfCriteriaColumn)+1),),COUNTIF(CriteriaColumn,"<>"&Criteria)+ROWS(A$1:A1)))"
I'm using this formula in a roster scene to pick up people that call in sick and display their restored job in a different cell. The problem that I am having say for example employee one calls in sick at 2 pm and i assign that job in a corresponding cell to another, then employee 2 calls in sick for the 1pm shift (the call was made after i have already restored employee one a replacement), the formula automatically places the 1 pm in the cell above the 2pm.
Is there a way to stop it from changing the value once a value is entered in a corresponding cell?
Link to the original formula thread. [URL] ........
I am trying to do a two way lookup with multlple results. In the example attached I want to know the names of the people who were in Boston on 01/02/09. I have tried a number of index, match formulas to no avail.....
View 3 Replies View RelatedI have been searching all day about this topic and while there are many "solutions" none of them fits my criteria and I can't figure out how to tailor it.
I am using a very basic data sheet to populate a purchase order. I want to be able to search a style and have all the data (color, units and price) automatically fill in upon entering the style number, I have been using vlookup but this only works if there is one color per style.
I have found different lookup functions that give the value +1 when the data is sorted but it returns the value even if it doens't fit the search criteria, I only want to return the second value if it correlates to the initial lookup
It highlights (with an x) all data that appears in both sheets a and sheet b. However I want it to highlight all the ones that are no on sheetA.
View 10 Replies View RelatedI have a lookup that needs to average multiple results for the lookup... columns
Q to W
Looks like it is only returning the first found value.
I am trying to find a function that will scan down a column to find a particular criteria. I would like all the cells that meet this criteria to have their entire row pulled through to a new sheet.
For Example, In the attached spreadsheet, I would like everyone in sheet 1 to have their details pulled through to sheet 2 if they are in class 1, sheet 3 if they are in class 2, and sheet 4 if they are in class 3.
I have a cell that I must remove the first 2 characters "RO" for each value in a column on a sheet called RAW DATA and put into a cell on a sheet called ROSS DATA. Some of the values in that cell have 3 digits after the RO and some have 5 digits. To do that I used
=REPLACE('RAW DATA'!A3,1,2,"")
Then I need to use this new resultant string as the lookup value in a VLOOKUP. The VLOOKUP will be looking at a named range called DAP on a sheet called DAP, in column 5 for an exact match and I need it to return that value to the cell.
I have tried using the indirect to no avail in different ways, and not sure that I fully understand the usage.
I have some data arranged in columns/rows as follows:
Location Name
--------- ------
United States Sarah Buchannan
United States Walter Smith
France Phil Barney
Italy Anna Wilson
Germany Philip Watson
France Neil Anderson
I want to have some function in my spreadsheet that will neatly present the names of the people at a given location in a separate part of the worksheet
e.g. a display of all people in France would have something like:
France
-------
Phil Barney
Neil Anderson
Ideally I would like to populate cells rows in a different column with the multiple results of the lookup ("persons at a given location") and that change in location for any individual would result in the list of persons at a given location being automatically updated.
Did alot of digging around and managed to construct a UDF that would display the results of the lookup in a single cell - it is however not very readable. But, I found out that a cell based UDF is not allowed to populate other cells other that the one where the function is entered!
I would like a formula to do a lookup multiple results but in a sorted matter.
Example: ....
1st sheet is giving me partial results with a vlookup - only gives me the first match to my ref number.
2nd sheet is the source data and contains refs and one or more matches.
NB: This is just a sample of the data, however in my original excel file with 25000 records there can be one or even up to 10 matches.
What I'd like to achieve;
Sheet One; Column B should give all matches for the ref number found in Column A.
It should merge all these matches to fit one cell so that all matches are sorted one under each other with line breaks.
As there is 25000 records to treat I would rather not have to do anything manually!
Am I asking too much?
I've read on the forum that it's best not to merge but I don't know how to get around the fact that I need all the matches per ref no in one cell to reimport into my database.