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


ADVERTISEMENT

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

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

Create List Of Calculated Results

Nov 14, 2006

I have created a formula that returns 2 dates and the number of days that lie between those dates (less weekends)..

eg.

Friday 22/12/06 Monday 8/1/07 10 days

What I would like to do, but can't work out how, is create a button (and a function or macro or whatever) that transfers that data to another sheet, adding to columns of previously calculated data.

View 9 Replies View Related

Create A List Of Results Based On Criteria

Sep 21, 2012

Col A has list of order numbers, col D has list of corrisponding order status (open , closed etc etc)

I simply want a formula to display a list of all the 'Open' order numbers in a separate sheet.

Obviously to size of the list will vary depending on how many open orders there are.

View 1 Replies View Related

Array Formula To Create List Of Filtered Results

May 15, 2014

I have a sheet of data that I can filter (e.g. only show male pupils).

On the next sheet I want to display the list of filtered results with no gaps.

As the user will be able to select which column of data to show from the first sheet I am referencing it as follows:

INDIRECT("'Progress Matrix'!"&ADDRESS(MATCH($B$6, 'Progress Matrix'!$A:$A, 0)+1,
MATCH(C$6, 'Progress Matrix'!$1:$1, 0))&":"&ADDRESS(300, MATCH(C$6, 'Progress Matrix'!$1:$1, 0))))

(However this formula no longer seems to work as I think I changed something).

I've tried to incorporate

SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))

to only show filtered results but with no success.

Progress Tracker.xlsm

View 6 Replies View Related

Create A Drop Down List And Use Lookup To Get Corresponding Items

Nov 12, 2009

I am making a spreadsheet for food and calories, On sheet one i have a meal tracker, I want to have a drop down list in colum 1 that references the food list on sheet 2, column A=food, column B=Protien, column C=Carbs, column D=fat. The values from Sheet 2 columns B-D would then be inserted into Sheet A in the respective slots. I have fiddled with vlookup and tried numerous things but I can't seem to figure this out.

View 3 Replies View Related

Dropdown List And Lookup Function

Mar 18, 2013

I have been writing a speadsheet using drop down lists and the Lookup function to link names to body weights. When using the dropdown list it displays for one name the incorrect weight. All the rest work correctly but one will not. From the list below when I select the name White it displays '68' rather then '80' as it should. If I change the name of White to the number 4, it will act correctly and display the weight '80'. It seems rather bizare.

This is the function that I am using

=LOOKUP(C53,Weights!B2:C9, Weights!C2:C9)

Pilot
Jones
78

Smith
95

[Code] .....

View 5 Replies View Related

Lookup List Using Offset Function

Apr 17, 2012

I am looking to do lookup list using the offset function. Scenario: I have 5 dealer groups, each with there own dealers. Each dealer has a list of customers. Once I select the customer it will bring up a list of their vehicles. I am stuck on selecting the customers and vehicles once the dealer group and dealer are selected.

View 1 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 / MATCH Function To Lookup The Owner Name Typed In Cell

Jan 2, 2009

I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.

For example, the data source contains different pets, their names, ages and their owners.

The other sheets are on a one-per-owner basis.

What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:

John Smith (in cell A1)

Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4

View 7 Replies View Related

Lookup Table (adjust Which Column The Lookup Function Refers To)

Jun 12, 2009

I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.

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:

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.

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

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

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

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

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

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:

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!

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

Tabled Lookup: Able To Lookup If Anywhere In A Cell Contains A Word From A List Of Words, And Then Provides An Output

Apr 27, 2009

I want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.

Column G:
VAT payment
HMRC payment
Pay VAT

I have a table on the side that shows:
Column Y Column Z
VATHMRC
HMRC HMRC

ie. If anything in column G matches one of the words in Column Y, then output the Column Z. I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.

View 3 Replies View Related







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