Lookup Function - Looking For Next Highest Value
Apr 23, 2006
Im just setting up a spreadsheet for structural timber design calculations. One of the requirements for this spreadsheet is to look up a Zx value from a table. Once the Zx value is found it returns a size of timber from another column. I have managed to create the function so that it looks up the Zx value and returns the size. However, if the function cant find the exact Zx value, which will usually be the case. The next lowest Zx value is used. Is there a way i can edit this function so that it returns the next highest Zx value instead of the next lowest. At the moment this is the code i am using.
= LOOKUP(Q92, 'Zxx Table'!E5:E172,'Zxx Table'!C5:C172)
Where Q92 is the value to be looked up. E5:E172 is the column of values to be searched. C5:C172 is the column of values where the result comes from. In addition to this just to further complicate things, can it return say the next 5 highest values? If a lookup function cant be used could you tell me another way that i can achieve this.
View 7 Replies
ADVERTISEMENT
Dec 2, 2008
I have an excel work book that has distances in column A from A2:A10 like so:
ABCD
15013401400
240
3900
4800
5600
61400
71200
81100
91150
101600
I would like to compare the value inputed into cell C2 against the distances and return the exact match or next highest distance to D2 if there is no exact match.
i.e. If 1340 is put in Cell C2 I would like excel to return 1400 to cell D2 as 1400 is next highest distance from 1340.
Here is the kicker, the distances cannot be sorted and I would prefer not to use VBA but I will if i have to.
View 9 Replies
View Related
Jun 18, 2009
The following calc works perfectly to find the latest entry in a column of meter readings in one worksheet and return the value to a master worksheet.
View 4 Replies
View Related
Oct 26, 2006
I have a table of information which is sorted in ascending order. I have created a vlookup to find the highest value as close to 1 as possible.
Say it returns the value 0.95.
i now have another cell which i need to find the second highest value from the column of data.
however i dont know how to do it. because the second vlookup function just returns the 0.95 all the time and not the second highest value..
E.G.
Column A
0.70
0.75
0.81
0.84
0.87
0.90
0.95
So in the cell with the first Vlookup i get the value 0.95 returned.
I need then for the second cell to return the next highest value in this case 0.90.
Then third cell third highest
fourth cell fourth highest value etc.
View 9 Replies
View Related
Jan 26, 2009
I have a spreadsheet that is comprised of 3 columns:
Column A - a list of values
Column B - the rank of the value in the adjacent cell in column A out of all values in column A
Column C - the quartile rank (1, 2, 3 or 4) of the value in the adjacent cell in column A
I would like to create a formula that would return the highest value in column A that is ranked in the 2nd quartile.
View 3 Replies
View Related
Aug 20, 2014
I need a formula that will give me a persons name that is stored in Column B based on the highest value within 5 named ranges. Each named range is a selection of every other cell in individual columns i.e. named range junior1 is cells in column E, named range junior2 is cells within column H and so on.
I've attached the worksheet as it as present.Sample Running Scores 3.xlsx
Cell Y3 shows the highest individual score shot (not handicap score) from columns E,H,K,N,Q (or more accurately the named ranges Junior1, Junior2, Junior3, Junior4, Junior5 within these columns). Each of these columns represents a different round in each league, in this case a Junior National League. I now need Y4 to show me the name of the person that shot the highest individual score, in this case Person 21.
I know I need to use a combination of Index & Max in some way but I can't work out what or how.
View 8 Replies
View Related
Dec 21, 2006
I am looking for formulas for cells B21 and B22 that will return the value from column A corresponding to the occurence of the highest/lowest value of Index compared to cell B:19, that is, the most recent Index data.
This seems to be an excellent candidate for LOOKUP as the data in Column A are unique and sorted.
Then, we have cells B:25 and B:28. On what "Week Ending" did the Min/Max value occurred?
Bonus Question, if Excel encounters more than 1 value that satisfies the formula, what happens?
************************************************************************>Microsoft Excel - Book1.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB24C24B27C27=
ABCD1Week*EndingIndex*AIndex*B*208/11/06561.2385.9*308/18/06561.1382.2*408/25/06556.5375.9*509/01/06566.3389.7*609/08/06584.2410.2*709/15/06595.8397.9*809/22/06566.5463.8*909/29/06633.9375.9*1010/06/06599.1383.3*1110/13/06585.8384.7*1210/20/06588.6382.4*1310/27/06570.8375.6*1411/03/06620.9402.2*1511/10/06620.9402.2*1611/17/06623.6401.4*1711/24/06599.0406.7*1812/01/06647.6426.6*1912/08/06612.2393.4*20****21Lowest*Since***22Highest*Since***23****24All*Time*High647.6463.8*25Date:***26****27All*Time*Low556.5375.6*28Date:***29****Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
May 20, 2014
I have a spreadsheet where columns I through V are date fields (stored as text as some have letters after the date). Is there a way that I could write a formula so that Excel returns the column letter of the last cell without a letter at the end of it? So it kind of looks like this:
I J K L M
05/01/2014 05/15/2014 05/30/2014(p) 06/20/2014(p) (null)
What I want in the column with the formula then would be just the letter J. Is this even do-able?
View 3 Replies
View Related
Dec 11, 2013
Suppose I have a column filled with numbers in fraction. And correspondingly I have a specific name for every 10 numbers. I want to find out which is the maximum among the 10. For this I could use the "MAX" function in excel. Similarly I have to find the maximum among the next 10 numbers. Since I have about 200 such numbers under 20 different names and eight such sheets (10 numbers per name), it becomes difficult to type the formula and select the range everytime. I would like to know if there is any shortcut for getting this highest number among the 10 numbers.
Eg.
Name Value Number
TU 489.662.272
TU 380.450.0379
TU 331.970.0475
TU 309.641.004
TU 300.890.1215
TU 288.840.0501
TU 280.090.022
TU 278.580.0115
TU 270.160.0397
[code]....
View 6 Replies
View Related
Feb 17, 2014
Basically, I have a worksheet that shows test results for 6 separate tests. If an objective is met in a test then it is marked with an "X". If an objective is partially met then it is marked with a "/" and if it is not met then it is marked with a "." . I have now created a combined column where I want to collate whether an objective has been met at all across the 6 tests.
For e.g. if in the range H14:M14 there is an "X" then N14 should show "X". If there is no "X" but there is a "/" then N14 should show "/" and it there are neither values in the range then it will show "." .
I tried
[Code] ....
but it just came back with a formula error.
View 3 Replies
View Related
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
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
Jul 29, 2008
I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.
View 9 Replies
View Related
Apr 18, 2008
Is it possible to set up a lookup function with two lookup values? For example, say I have a list of items such as:
1 A 14
1 B 22
2 C 84
4 D 25
I'd like to have the lookup go to the above table and find the number 1 and the letter B and return 22. I can't seem to visualize how to make this work.
View 9 Replies
View Related
Nov 27, 2013
I'm attempting to put together a script which in simple terms finds the 'Highest and Second' highest activity and associated FTE for a given sub group.
To be more specific:
On the "Slide 1" sheet look at the values in column J starting at row 7 until blank;For each value, then search the "All Monthly Direct Activities" sheet in column C for the same value, excluding the 'Subtotal' rows;Where a match is found, compare all the values in column E for that sub group;And Find the highest and second highest figure;
Once these are found copy this figure and associated value in column B and paste onto the "slide 1" sheet.
I'm not particularly well versed in writing VB, but I've been working with the code to see if I can get this work, which I have been unable to do.
I have attached a file which may perhaps provide a greater detail than my description of the problem containing the 'Source' and 'Destination' sheets.
View 9 Replies
View Related
Jan 15, 2008
If I use =MAX(A1:G1) I get the highest value. Is it possible to show the next highest rather than the highest and if so how?
View 9 Replies
View Related
Feb 3, 2009
Can I get all the information from Employee & Benefits tab to populate in the 401K worksheet. All information is on these two sheets except status, plan entry date, and 2009 compensation.
True under Employees tab means that a person is still active and I would like for that date to populate the 'Hire Date' column; whereas, 'False' represents a person is termed and I would like for the false date to populate under termed. Can you use the social security number if its a V Lookup because I have thousands of rows and multiple people with the same last name.
View 5 Replies
View Related
Apr 22, 2009
How do I write a code for the following Excel formula:
=LOOKUP(E20,{0,2501,5001},{"CatA","CatB","CatC"})
I tried the following but not working
View 3 Replies
View Related
Oct 23, 2008
I would like to do is reference a table of data using the Vlookup function but add all of the results together. I have a table where I keep track of orders being distributed, and in an inventory sheet I want it to reference that table based on the serial numbers entered and total the results.
View 3 Replies
View Related
Nov 21, 2008
A B C D E
1 DAY 1DAY 2DAY 3DAY 4
2 AAA1638
3 BBB7325
4 CCC3847
5 DDD5562
This is what I have. Now I want to see, for each day which has the highest value. so it should return BBB for day 1, CCC for day 2, DDD for day 3 and AAA for day 1.The formula I am using:
=lookup(max(B2:B5),B2:B5,A2:A5)
=lookup(max(C2:C5),C2:C5,A2:A5)
=lookup(max(D2:D5),D2:D5,A2:A5)
=lookup(max(E2:E5),E2:E5,A2:A5)
Tell you what, it is giving me correct result for column B,C,D but not for E. The formula for column E should return AAA, but for some reason it is returning DDD.
View 2 Replies
View Related
Jul 14, 2006
I just wanted to use lookup function in my code. But I had to re-write my for..next statement 8 times. How can I state it in one single lookup function used in a for..next statement. Here is my code
Sub iteration(r)
Dim i As Integer
Dim j As Integer
Dim x1(4) As Double
Dim arra(8, 2) As Double
Dim arrb(8, 4) As Double
r(1) = Worksheets("sonuc"). Range("j4").Value
r(2) = Worksheets("sonuc").Range("j6").Value
Dim myRange As Range
Set myRange = Worksheets("giderler").Range("b6:b11")
x1(1) = Application.WorksheetFunction.Floor(r(1), 0.5)
x1(2) = Application.WorksheetFunction.Ceiling(r(1), 0.5)
x1(3) = Application.WorksheetFunction.Floor(r(2), 0.5)
x1(4) = Application.WorksheetFunction.Ceiling(r(2), 0.5)
For i = 1 To 4
View 6 Replies
View Related
Sep 21, 2006
I have 2 worksheets, 1 containing a list of 5000 products and 1 containing a list of 3 columns with classifications in them (about 300 classifications). for example worksheet 1 has 5 products
Coke
Volvic
Mars Bar
Cornflakes
Bags Maltesers
The 2nd worksheet has 3 columns with the following info
Column 1 Column 2 Column3
(Catagory) (sub Cat) (Sub Sub Cat)
Minerals Soft Drinks 2lts
Minerals Water 1lts
Sweets Choc Bars Std Bars
Sweets Bagged Lines Std Bags
Grocery Breakfast Cereals
I want to be able to add a classification to the products in worksheet 1. For example to be able to choose a dropdown that has a list of each classification
and after coke select from the "Catagory) drop down, then select the "Sub Cat" drop down and then select the "SubSub Cat" dropdown.
Product Catagory Sub Catagory SubSub Catagory
Coke Minerals Soft Drinks 2lts
Mars Sweets Choc Bars Std Bars
As I said I have to do this manually for about 5000 items so I need to be able to do this as fast as I can. Can you use the lookup fuctions or something like that?
View 6 Replies
View Related
Jun 27, 2007
how to fix the REF error in this formula. =IF((OR(G3="Hour (Truck)",G3="Hour (Truck + Dog)")),(VLOOKUP(G3,Rates!$A$186:$B$187,2,FALSE)),(IF(F3="Hanson",3.5,(IF(F3="Lakes",10,(IF(F3="NO",(VLOOKUP(E3,TonnesRates,MATCH(B3,Rates!$B$110:$D$110),FALSE))))))))). I have tried to attach the file with the minimal information in it to make it small enough but I can't get it below 48kb.
View 7 Replies
View Related
Aug 1, 2014
I have read through other similar topics and tried using the index and match function to lookup but been unable to make the formula work.
I am trying to use the lookup function to pull data from a table using criteria in two columns and one row. I have attached a spreadsheet for example.
I want to use criteria of Day (eg . 1), Total height (cm), and jar # (eg. 1). I inherited this table from a previous coworker and don't have time to organize the data better.
In the spreadsheet I want to return the value of 8.8 (highlighted in yellow). The criteria I want to use to lookup the value are highlighted in red.
View 4 Replies
View Related
Mar 25, 2009
The following Lookup-function does not work properly. Ay idea why? My Input in E13 is months.
=LOOKUP(E13, {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},{"012","001"," 002","003","004","005","006","007","008","009","010","011"})
View 5 Replies
View Related
Nov 12, 2009
I am creating a spreadsheet to keep track of money and grouping them into categories. I have a column to group a purchase into a category (column E) and the amount spent in column F. I want column T to add up all of the purchase that were made according to the category in Column E.
View 2 Replies
View Related
Dec 7, 2006
I have store numbers and store volume groups (one letter indicating their sales volume). This is sorted in somewhat of a table - the volume groups are in row 4 going horizontal, the store numbers are under their respective volume groups going vertical.
I have all store numbers listed vertically on another sheet, starting in A2. I want to search for the store number in A2 within the volume group data and return the appropriate group header when it's found.
View 14 Replies
View Related
Nov 16, 2012
A
B
C
Ap1
queens Carrots
Goja Apple
Ap2
fujja Apple
fujja Apple
Ca23
Goja Apple
queens Carrots
I tried to write a function so that it would search for a word from Column C in Column B, and then when it found it, give me a a combined result of Column A and B. For instance. I lookup up C1 (Goja Apple) in the range of all of column B; the function finds it in B3 (Goja Apple); then it returns the combined result A3 and B3 (Ca23 Goja Apple). I tried the =lookup, but I think the first column you select must be in ascending order, and that wont work for me.
View 3 Replies
View Related
Mar 26, 2014
In my spreadsheet, one cell in every row has a string that contains the invoice number. I would like to get the invoice number in a cell of it's own. The problem is that different vendors use different lengths of characters for their inv #, so I can't do a one size fits all MID formula.
What would be the best way of getting the invoice number?
View 9 Replies
View Related
Apr 10, 2007
I'm trying to set a range to use in a Lookup function but it's not working. I've done this other times with no trouble and I don't see why it's not working now.
If Target.Row > 2 And Target.Count > 1 Then
For Each c In selection
If c.Column = 11 Then
' confirm that Activity ID is in the Reference file list
Set Lookup_Vector1 = Worksheets(c.Worksheet.Name).Range("Activity_ID")
Error occurs on the Set Lookup_Vector1 line, Run-time error '1004': Application-defined or object-defined error.
The range Activity_ID is defined in the current worksheet but exists in another workbook. The c.Worksheet.Name correctly gives the name of the worksheet where the range is defined.
View 9 Replies
View Related
