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.

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.

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.

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.

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.

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.

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.

I have a table of data (say Column1 to Column 5) with multiple rows.

Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.

I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.

It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.

Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?

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.

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

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.

I'm making my own gradebook (attached) and one of my sheets will list scores for each student in different assignments. I have one sheet which keeps track of all students and all assignments with other info. I would like to program cells in one sheet (the third in the attached file) to lookup a particular student's grade in a particular assignment. I figured trying a LOOKUP with an AND requirement might work but it keeps returning the message "could not find value".

My formula references the student's name and the assignment from the identifying cells so that it is easy to copy and paste. I wondered if it was this which resulted in the error, but doubt it.

I have a worksheet that contains a number of rows. [Thank you, Captain Obvious!] These rows are logically grouped to represent a series of "items", with each item having one or more rows (with a "header" row at the top). The rows for a particular item represent different types of information about that item, and therefore make use of different formulas based on the row type. Due to the relative complexity of the formulas in these rows, I am creating a series of row "templates" from which I can copy and paste every time I want to create a new item or add rows to an item. As such, I am trying to avoid direct cell references to different rows, as these would easily get messed up during cut and paste. Instead, I am trying to limit myself to various lookup functions (e.g. OFFSET, MATCH, etc...) that will work regardless of where a row is added.

In order to accomplish this I am using a "key" column to group all rows for a particular item together. Until now I have had to type in the key value for every row for a particular item. What I would like to do is to only have to type the key value in the header row, and have a formula populate the key value in all subsequent rows ... until a new header row is reached, at which point the new key value will be used. Take a look at the following example: ....

I'd like to record the highest / lowest value in a single cell without it being written over i.e record the highest value and if there is another value lower it wont overwrite it.

I've tried using the =max or =min but whenever a newer value appears in the cell it just follows that without keeping the higher value?

This is for an online sports management game I play. Basically I have calculated each players skills in each position, and I need to find the best combination of the players on the field to give me the best team.

I have a table as below, and I need to find a solution that gives me what the best combination will be....

I'm trying to list the latest revision of a drawing in an issue sheet spreadsheet. What I need is to be able to enter a formula that will find the highest alphanumerical value in the columns beyond. Alphabetic characters take precedence over numerical - i.e., drawing issue numbers usually follow a 00, 01, 02, 03, then switch to A, B, C, D,... order.

To try and illustrate what I mean a little better, I have the following table, with dates in DD/MM format. Sorry it's not laid out too well, my HTML isn't great:

I won't have control over the numbers and letters reached (i.e., the drawing revision could get up to Z or something - or the number revision reach 15 before switching to alphabetic revisions) so I'd rather avoid having to set up a crazy IF function with "00", "01", "02", "03", "04", etc etc. But -

I am starting to use formulas and I am constantly amazed at the power of Excel. I am trying to write a spreadsheet and I'm having a problem getting Excel to do what I want; I have 2 column, each cell in Column 1 will have a numeric value changed daily. I want Column 2 to retain the highest value that has been added. i.e. If I type 4 in A1,(for example), on Monday, then B2 will read 4, if I type 5 in A1 the next day, then B2 will change to 5, but if I type a value less than 5 in A1, then I still want B2 to read 5. The value will be input daily for an indefinite period and needs to be user friendly. I can't work out how to achieve this.

What is the best way to look up and return the 10 best performers out of a list of say, 40. I have columns representing people's names, their school's name, their age and their total score. The following formula is to add up the 4 highest scores for each school: SUM(LARGE(($A$2:$A$499=Y2)*($C$2:$C$499),{1,2,3,4})) (entered as an array) Now I want to display the 10 best performing schools (who will be determined by calculating their 4 best scores).

I have a massive amount of data made up of values taken every five minutes over several months. I want to find the highest value in a day for every day. I'm wondering if the best method is to define each day as a dynamic range and then use the max value command to get the highest value?

I need a formula that can lookup the sell price with the highest frequency. For example, I need to look up item 2100 in sheet2 and sheet1 contain data for vlookup like this:

I have a row of numbers (per example below) and I would like to return the column header for the max value and the second highest value, even when the second highest value is the same as the max value.

For example

A B C D E Highest Second Highest 1 2 3 2 3 C E 3 1 3 2 2 A C

The return values are under the highest and second highest column headers?

I'm trying to wrap up a spreadsheet and I'm trying to figure out how to have my formula use the highest of 2 numbers when making a calculation.

For example:

If: Cell A1 = 5000 Cell B1 = 3828

I want the formula to use A1 in this case. However, if the total of B1 = 6000, I want the formula to use B1 instead of A1 in the calculation. (A1 = 5000 will always be a constant/base).

I have a large spreadsheet of data that contains 3 columns: Columns are Name, Revision, # of items. I want to find the # of items for the highest revision for each name. List looks like this: