Lookup A List Of Values & Average Results

Jan 25, 2007

I have a simple one today but on a time crunch so I don't have time to go through the book.

I am trying to hlookup a list of values from a table and average the results. The values are setup horizontally.

View 9 Replies


Average Multiple Results For The Lookup

Dec 11, 2008

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

View 3 Replies View Related

List Multiple Results From Lookup On A Different Sheet?

Aug 28, 2013

I need to start a list in cell a8 on sheet1. I need it to find and list multiple results vertically. It will lookup what is in cell a1 on sheet1. The table of info is on sheet2 from a1 to b44. Column a on sheet2 has the values of what is in column a on sheet1 and column b is what I need returned to the cell with the formula.

View 3 Replies View Related

Create List Of Results With Lookup Function?

Mar 16, 2012

I'm working on an invoice generator, and I want to add a "lookup by phone number" feature. I've got an invoice database with invoice info stored horizontally for past invoices. On a separate sheet, I want to be able to type in the phone number in range ("O11:Q11") and have a list of invoice numbers and dates matching that phone number appear in Columns R and S respectively.

Here is some workbook info:

Invoice generator is in 'phonelookup'
Invoice database is in 'invoice database' and the table is called CompInvoices
In CompInvoices, Invoice numbers are col1, dates are col2, and phone numbers are col7.

View 1 Replies View Related

Lookup Results Depending Upon Cell Values

Aug 25, 2008

Depending upon a figure entered e.g. = $ 895.00 and also Depending upon a state being entered e.g. = NSW. Then Excel will lookup a series of cells to produce the correct figure depending upon the correct data ciritera being met. I have a data table sitting behind the spreadsheet with all relevant values.

View 3 Replies View Related

Average Largest Several Values In A List

Dec 29, 2009

I have lists of numbers in a columns and would like to elliminate high outlier values in each column. Each list is different so there's no way to just set a max value and say that any value above it is an outlier. What I'd like to do is to average the top 5 or so max values for example, and then say that any vale above this average is an outlier. So a formula such as =average (max1,max2,max3,max4,max5) would work if it's possible to derive max1, max2 and so on. Or maybe there's a different way of accomplishing this?

View 3 Replies View Related

Average Of List Of Cells VLOOKUP Values?

Jun 20, 2014

I need to find the average of a list of text cells. My list is in column A, and in column B I've assigned each of the text cells with a numeric value. I'm then doing the average on Column B which will return a numeric value which will represent one of the text values in column A.

My average formula then looks like this:


Can do this without using the helper column B and do an average formula using a VLOOKUP?

View 3 Replies View Related

Average A List Of Values But Some Values Contain A Zero

Aug 13, 2007

I have a list of values on my spreadsheet in a similiar order to this:


I use the average function to work out an average for all the values, but since the value of 0.0 is showing i should not include this as an average. the range of values above (8) will always be the same for the month - so i need to find a way of omitting the 0 value without keep changing the formula

View 9 Replies View Related

Lookup - Bring Up List Of Multiple Possible Values?

Mar 25, 2012

I have a worksheet where data has been entered from a userform. For each entry, there is a separate reference number in the form of contract number-schedule number-machine type-cage number (e.g. 123-123-ABC-1).

The machien type will not always be known so the user will need to do a search for the contract number-schedule number and then bring up all the possible reference numbers.

For example this could look like:

I would like this list of all possible values to pop up.

what look up function would work and how I can do this?

View 5 Replies View Related

Lookup Value From Database And Get List Of Matching Values To Another Sheet

Sep 21, 2013

I want to find the corresponding Disc Codes from a list and copy them in the DiscName column in the summary sheet. some lab names will have more than one Disc codes so when I run the macro it should bring up all the relevant Disc Codes matching with the Lab name to DiscName column.

Col 1 col 2 col 3 Lab name Disc Name(say abcd) xxxx yyyy zzzz ppppand

The list looks something like this.

Col 1 Col 2 Lab name Disc nameabcd xxxxxabcd yyyyyabcd zzzzzabcd pppppbcda
qqqqqbcda rrrrrbcda iiiiibcda jjjjjbcda kkkkk

View 1 Replies View Related

Lookup To Return Multiple Values And List Sequentially

Mar 17, 2008

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.

View 7 Replies View Related

Dropdown List Always Shows Unique Values From Column Based On Lookup Value

Jul 22, 2014

I have a worksheet (say, Sheet1) that I am going to manually import a large data set into on a weekly basis for reporting purposes. One of the columns from this data dump will have the header "Design Id" in the first row, but it may not be in the same column every time. I am trying to figure out how to create dropdown lists on a separate worksheet (say, Sheet2) in the same workbook where the data source always looks up the column containing the header "Design Id" from Sheet1 and then returns only the unique values from that column as options in the list.

View 1 Replies View Related

Create A Formula To Average Survey Results Per Instructor?

May 28, 2014

I am trying to come up with a good way to average a particular question in a survey result for instructors, however the caveat has me pretty puzzled. We're looking to be able to filter by mm/dd/yyyy date (which pulls mm/dd/yyyy hh:mm from raw data) and then have one average of all scores for each instructor listed. It gets a bit complicated in that the actual survey has 6 drop down boxes with all the instructors names, so one instructor could have data for the same (or any) date in any of the 6 colored columns (name, score, qualitative x 6).

My initial thought was to list out the names on a second sheet and then have some sort of averageifs to combine the columns, but I am a bit lost on how to take the dates into account, let alone how to sum the columns then average.

View 2 Replies View Related

Sum All Results Of Lookup

Aug 30, 2007

I 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

View 4 Replies View Related

Lookup Results Using Name Manager?

Mar 14, 2014

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:


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.

View 9 Replies View Related

Lookup With Multiple Results

Oct 29, 2009

After a lot of searching and trying I still didn't get to something that works.

My situation:
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!

View 9 Replies View Related

Lookup Results For 1st & Last Day Of Month

Dec 30, 2006

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.

View 4 Replies View Related

Concatenated All Results Of Lookup

Aug 29, 2007

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"

View 9 Replies View Related

Lookup Number Results In #N/A

Sep 8, 2007

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 Related

Lookup And Return Multiple Results

Mar 31, 2014

I want to look up a particular value and return according results horizontally and vertically. Attached is the excel.

View 10 Replies View Related

Lookup Value And Return Multiple Results

May 4, 2014

I have been using this formula to do lookup and return values


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

View 10 Replies View Related

Two Item Lookup Multiple Results

Jan 15, 2009

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 Related

Returning Multiple Results For One Lookup Value

Jan 28, 2009

I 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

View 5 Replies View Related

Finding Results Not In A Table Lookup

Jul 31, 2007

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 Related

Lookup And Pull Results Into New Sheet

Mar 27, 2009

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.

View 2 Replies View Related

VLOOKUP Using Results Of Formula As LOOKUP VALUE?

Oct 25, 2013

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.

View 9 Replies View Related

Presentation Of Multiple Results Of A Lookup

Aug 17, 2005

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:

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!

View 9 Replies View Related

Lookup Multiple Results Sort

May 23, 2007

I would like a formula to do a lookup multiple results but in a sorted matter.

Example: ....

View 9 Replies View Related

Lookup All Occurences & Merge Results

Oct 21, 2008

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.

View 8 Replies View Related

Return Lookup Results Of All Occurences

Jan 30, 2008

I am using vlookup to return data from a range that could have the same name in column A.

There are 8 columns
Column 1 is Name
Column 2 is Sun
Column 3 is Mon
and so on.

The problem I have is that a name can appear in two places in column1 with relevant information in different columns:
Fred appears twice, on Wed he is on a course the rest of the week he is on leave. I need a C to be returned for Wed & A/l for the rest of the week.

View 9 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved