How To Return The Maximum Value In A Multiple Results LOOKUP
What I am try to do is look up the highest (maximum) date in a lookup up that has multiple results.
Please see attachment.
Column in question is 'U' (FinishDate)
Maximum date that I want to recover is in column 'P' (DATE_COM)
LOOKUP VALUE is found in column 'A' (QUOTE_NUM)
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Lookup Single Value In One Sheet, Return Multiple Results From The Other Sheet
i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab) what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table RANGE = Data!A2:K255 the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit) is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset after some MASSIVE googling, i have stumbled accross this B1 = Search box (txt field) A6 (which will be a hidden column) contains =MATCH($B$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6. this is supposed to look for the next row number which contains a match and provide that row number and througout my other columns, i have B6=OFFSET(Data!$A$1,A6,1) B7=OFFSET(Data!$A$1,A6,2) B8=OFFSET(Data!$A$1,A6,3) and so on 2 things i cannot recitify.. 1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats" 2, it comes up with multile .. irrelevent results.
View Replies!
View Related
Lookup Maximum Value & Return Corresponding Row
i have a matrix index| a| b| c| 0001 |1| 2| 2.22| ( probably it will be much clear to see the attachment) I need to lookup for the largest number in B2:D2 (in this case 2.22) and return the corresponding header ("C"). I though it would be simple with hlookup() and max() but i can't make it work.
View Replies!
View Related
Lookup Maximum & Minimum. Return Corresponding Row
I have searched your forums and thought I had found a sufficient answer but could not get the vba to work. So any help is greatly appreciated. I am trying to determine a max value from a list then put that value in a cell. Next I want to determine how many times and on what day that max value occured. From there take the value and concatenate them adding a "," between them I have attached an example. I would like the values placed in cells F1 and H1 (the other is a min value and when it occurred)
View Replies!
View Related
Return Lookup Results Of All Occurences
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 Replies!
View Related
Lookup All Occurrences & Return Results To 1 Cell
My task is that I have several documents exported in excel. The requirements in them were mapped to another document. The IDs were populated in a certain column but are now no longer needed. I have to convert those old Ids to the current ones. In my example I have populated Sheet one with the lookup table. In column A are the old IDs. In Column B are the new ones they map to. The old ones can map to several which is why there are more than one ID in some of the cells. In column D of Sheet 2 I have populated it with the old Ids. In Column A I'd like to have the lookup return the appropriate maps to the new Ids. For example for the first map in cell A2 it should return: abc_18 abc_43 abc_6 abc_4 all in one cell. Vlookup can return the first hit (abc_18 and abc_43 for cell D2), but ignores the other two IDs in cell D2. I was able to work around this combining the find, mid, and vlookup to return all values. The problem with that is that the formula gets monstrously big after just having it return 4. In the actual files it can be up to 30 IDs which would make that formula literally pages long since. I have pretty much no experience with macros, but it seems like the only solution since I have to send this off to various people so they can use it.
View Replies!
View Related
Lookup With Multiple Results
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 Replies!
View Related
Presentation Of Multiple Results Of A Lookup
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 Replies!
View Related
Returning Multiple Results For One Lookup Value
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 Replies!
View Related
LOOKUP Multiple Results But Ignore Duplicates
I have this formula below it will return multiple results is it possilbe to modified it that it will return multiple results but ignore duplicates. I would like it to be a stand alone formula no helper cells or helper columns. =INDEX(B$2:B$15,SMALL(IF(A$2:A$15=E$2,ROW(A$2:A$15)-ROW($A$2)+1),ROW(A1)))
View Replies!
View Related
Multiple Duplicate Lookup Results In Single Cell
I have attached a copy of what I am trying to do. I've been researching vlookup for a while and everything I try doesn't seem to work. I'm also fairly new to Excel, so most of this is my first time trying these formulas. In the attached test.xls file, I have two sheets created. The first is "Responsibility," and the second is "List." The data in "List" is what I am trying to pull from. As you can see, the people's names are listed more than once as the list goes down. On the "Responsibility" page I have each person's name one time. In the "Extinguisher" column, I'm wanting it to list every number that is found next to the person's name on the "List" sheet. For example: Column B2 on the "Responsibility" sheet should read as follows: 1,2,3,17,24 Here is the formula I have in these cells: =VLOOKUP($A2,List!$A$3:$C$38,3,TRUE)
View Replies!
View Related
Return Multiple Results Based On Criteria
Please see the attached sheets. Here's the issue: On sheet "master" I have a list of goals in column A. Each has been assigned to at least one person. Each person will have their own sheet that will capture the goals assigned to them. As an example, see Al's sheet. I am trying to get Excel to look for all of the goals assigned to AL on the Master sheet and list them nicely onto AL's sheet without out blank rows. (and for each, respectively on their own sheet). I could do this with Pivot Tables if the data were displayed differently, but I have been told that I have to display the goal assignments as shown. I could use "x"s instead of their names to mark the assignment and I could combine the "goal-achieved expectations" and "goal #s" columns if it helps. I am not supposed to use filters either. Note: Of course, a goal or an assignment could change on the master sheet, but the assignment is more likely to change. how this done for AL, I can go ahead and create the other sheets.
View Replies!
View Related
Can Index Or Match Return Multiple Results
I've spent the last week trying to do the following, with little to no success....and I'm running out of hair to pull out!! I have a worksheet (let's call it DATA) with the following column headers: * Site Name * Year Initiated * Status plus a whole bunch of other columns of information. On a separate worksheet (let's call it RESULTS) I'm trying to create a summary of all the Site Names where the Year Initiated = "2004" and the Status = "Active". There will be multiple occurences where this is true. I then want to create further results where the year = 2005, 2006, etc., etc. I've been trying to use the INDEX function with variations of MATCH, SMALL, ROW, etc., etc., but so far, no luck. Advanced Filtering is not an option as I need to do further manipulating of the results on the RESULTS worksheet.
View Replies!
View Related
Vlookup Data And Return Results On Multiple Entries
So i have a spreadsheet that has a list of members and how many events they have attended. That is fine because i achieved this by doing a countif function on their account number. The spreadsheet has to sheets Events Attended TOTAl and List. In the list it has their name account number and what event they attended and what date. What i need is to have a function that will lookup their account number and return what event they attended but they might have been to 4 different events. I have a total of 5 columns dedicated to Event attended so we can tell it to lookup first event and return result then have an if function in the next column to lookup event and if its returned in previous column move onto the next event attended.
View Replies!
View Related
Lookup Multiple Same Value And Return Multiple Corresponding Value In Ascending Order
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.
View Replies!
View Related
Lookup And Return Multiple
I have a spreadsheet similiar to the below: COLUMN A.....COLUMN B Person 1.......Process 1 Person 1.......Process 1 Person 1.......Process 2 Person 1.......Process 1 Person 1.......Process 2 Person 1.......Process 3 Person 2.......Process 1 Person 2.......Process 4 Person 2.......Process 1 and so on. In the list, the "People" aren't neccessarily in order, for example, later in the list, Person 1 appears again. There are a total of 10 processes, and any person can do any number of each. What I ideally want is in Columns D, E, and F, is something like the following: COLUMN D.....COLUMN E....COLUMN F Person 1...........3.............Process 1 Person 1...........2.............Process 2 Person 1...........1.............Process 3 Person 2...........2.............Process 1 so a summary of the main data, showing how many each process each person has got. I have, and failed miserably, getting arrays to work but that might just be down to my own incompetence. I have researched into Countif's and Lookups, but it's not quite working. The data in columns D, E, and F needs to be fairly compact so ideally I wouldnt want any spare rows between the end of the summary data about Person 1 and the start of Person's 2 details.
View Replies!
View Related
Lookup With Multiple Result Return
I have an excel worksheet that consists of 4 tabs. Each tabs has approximately 60,000 lines of data. The data consists of user id's and menus that correspond to the user id. There are 131 user id's in total. I would like to be able to return all the menus for one of the user id's. If we need to start with one tab and work from there, that's fine.
View Replies!
View Related
Lookup To Return Multiple Values
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".
View Replies!
View Related
Lookup Multiple Conditions To Return One Result
I am preparing an "automated" order form. I have a list of products (ten) in a pull down list, a list of options (3) in a pull down list and a list of the pricing. So, If PRODUCT_A with OPTION_A, then PRICE = $X. If PRODUCT_A with OPTION_B, then PRICE = $Y. If PRODUCT_B with OPTION_C, then PRICE = $Z. etc. I want the user to select the product, then the option, and have the correct price "pre-fill" the cell.
View Replies!
View Related
VLOOKUP To Return Multiple Cells Of Same Lookup Value
I am trying to create an automated receipt for a list of artists. I have given the artist an individual reference so I am doing a VLOOKUP to return description/price value etc but I can only get it to return the initial value and I have approx 10-15 works under each reference. Maybe VLOOKUP is the wrong formula to use.. I have attached the file.
View Replies!
View Related
Lookup & Return Multiple Items
I am trying to make a search form in Excel 2003 where I can lookup data for a specific value which may have multiple rows. I have several columns, column A Tracking Number, B Order number, C Item Number, etc. For example; Order number S6001 has three item numbers on rows 1,2,and 3. I can get the data on row 1 using the basic "LOOKUP" function but not rows 2 and 3. How do I have all of the line items (rows) returned for that order number.
View Replies!
View Related
Lookup & Return Multiple Occurrences
In Sheet1 are unique VM entries (Column A) which have a Group (Column B), Time (Column C) and a Reference (Column D) which can be same for other VM entries. In Sheet2 are the unique Reference Entries (Column A) which need Group (Column B) and Time (Column C) entries populated from Sheet 1. Multiple occureneces need to go in one cell, I realise this is not the best idea, but for this purpose they need to be and seperated by a comma or semi-colon. In B2 I've used this formula: =INDEX(Sheet1!A1:D11,MATCH(A2,Sheet1!D1:D11,0),2) Which partly works, but only returns the first occurrence of H1 on Sheet1. I need all occurrences (i.e G1;G4;G8) returning for B2. I can only think that some VBA script may be needed but I'm not really an expert in this area. In the full spreadsheet there are hundreds of rows and multiple entries, where Group and Time need to be populated on the second sheet
View Replies!
View Related
Lookup Function Required To Return Multiple Matches
I have a list with 8 columns. In the majority of instances, Columns A & B will have duplicate information. I need to be able to search columns A & B & return the information from them PLUS all info contained on the same row's from columns C/D/E/F/G & H. EG:... My column headings are as follows... Type Region Contact Phone Email ADD1 ADD2 ADD3 In column A (type) would be the type of contact eg:business/personal/family etc. So... If looking for family I'd need excel to show me the region, contact, phone, email, add1, add2 & add3 for each family record. Hope this makes sense! There are over 1000 records i need excel to search through & return matches. I'm an intermediate excel user at best so finding it complicated. Tried vlookup but that stops when it finds a match & anything else seems over my head.
View Replies!
View Related
Lookup Multiple Columns And Return Top Row
I've been working this for ever and can't seem to figure out the best way to go about it. I have attached an example sheet. All I need to do is figure out the Dept #... which is listed in Row 1, Column F:H. I want to match the project numbers and then return the AA, BB, or CC in Column B.
View Replies!
View Related
Lookup Single Value In One Array, And Return Multiple Values
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.
View Replies!
View Related
Array Lookup, Return/Sum Multiple Values
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.
View Replies!
View Related
Lookup To Return Multiple Values And List Sequentially
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 Replies!
View Related
Lookup Data From Validation Drop Down And Return Multiple Corresponding Values
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.
View Replies!
View Related
Lookup With Multiple Criteria: Return The Amount Paid And Full Cost Based On The Person's Name And The Date
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate. Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
View Replies!
View Related
Lookup Maximum Value
I have a column of merged cells which contains a number e.g. 396322669097172 and a time e.g. 00:00:00. I can format this combined cell as a 'general', but think it can be formatted as any type of cell retaining this layout (after combination, prior I had to format both cells as textual), however when it is then not possible to get a maximum value from this column using a pivot table. The max comes back as zero. I have tried using some freeware to combine the cells and I don't know what it did differently but I was able to order the values using a pivot table afterwards.
View Replies!
View Related
Lookup With Maximum Value
There a a total of 13 sheets. Sheets 1-12 are named by months i.e. Jan, Feb, Mar, etc.. Each of these sheets contains a max temp for the day. There are cells at the bottom of each of these sheets that return the max, min, and average. The last sheet is an annual summary sheet. It collects info from the other 12 sheets and places them on it. There is a cell on the annual summary sheet that attemps (this is my problem) to determine the max and min temp for the year (easy enough). The problem is that once the sheet determines the max and min temp for the year to determine what day of what month it occurred on.
View Replies!
View Related
Lookup Value Above Maximum Value Of Another Range
How to lookup for greater than a given particular value in a list of data? Suppose the value to find is in cell C17 & the column to be looked for is F, in the attached sheet. Now the data range is from F8:L27. If the value in C17 is not present in Column L, then the value greater than the specifed value should be searched in Column L. I have tried using VLOOKUP, but cannot find for a range of values.
View Replies!
View Related
Lookup Maximum With Conditions
i have a list of price list, and it is updated once a while, which means there is a change in version number. The table is a direct extraction and it has to be like that... I have tried to vlookup but only return 1 line, same as match and index... or maybe im not using the forumla correct. Attached is the excel example hope this help. as you can see there is alot of dupe reference but different version and prices I cannot concatenate the reference and version number as my lookup reference doesnt know what one is the latest version... but one thing is constant, that is the version number is always needs to be the highest.
View Replies!
View Related
Return MAXIMUM Based On Criteria
i have a spreadsheet like the following A B C Country Revenue Month 1 UK 10 Jan 2 France 20 Jan 3 US 30 Jan 4 UK 25 Feb 5 US 35 Feb 6 France 5 Jan and so on... So where country = UK, France or US I want to retrieve the MAX revenue from all months and which month it was in. Eg UK max revenue was in Feb of 25. I am not sure how to apply the max formula with criteria. Is there any way to do this?
View Replies!
View Related
MAXIMUM Function Limit: Return A Value?
What I'm trying to do is get the MAX function to return a value, but that value needs to come before the values reach zero. It's a cubic equation that basically starts, at the beginning of the data range, under 10 then hits zero and ends up near 30 at the end of the data range. The end data range max doesn't matter to me if the data range hits zero along the way. Is there any way to make the MAX function stop at a certain value, or is there another function that could accomplish what I'm trying to do?
View Replies!
View Related
Return Maximum Date Or Blank
I am currently using an array to return the MAX date value. This formula will enter 1/0/00 if the referenced cells are blank. I'd like the furmula to LEAVE THE CELL BLANK if the referenced cells are blank. {=MAX(IF(Bid_Circuits=$A45,Bid_Trim_Completed,""))}
View Replies!
View Related
Return Heading Of Column With Maximum Value
In attached example, I have 3 columns of fractions with a final row of sumtotals (taken from elsewhere). I test to see which column has the maximum value in each row and allocate the result with the column's heading. Sometimes, two or more columns have the same max value. In this scenario, I wish to select the column heading that has the largest sumtotal.
View Replies!
View Related
|
TRACKER
