#### how to lookup a value within a range and return a label

```Wrecking my head this one:

For example if the Label
A  is from 1 to 200
B from 201 to 1000
C from 1001 and above

How can I lookup and return the correct label for a value.

Is there a function for this, or does it require programming
```
 0
jocmccoy (1)
11/16/2005 11:10:05 AM
excel.misc 78881 articles. 5 followers.

1 Replies
470 Views

Similar Articles

[PageSpeed] 45

```Put these values in cells A1:B3

1	A
201	B
1001	C

put the value to look up in C1, and use this formula

=VLOOKUP(C1,A1:B3,2)

"jocmccoy" wrote:

> Wrecking my head this one:
>
> For example if the Label
> A  is from 1 to 200
> B from 201 to 1000
> C from 1001 and above
>
> How can I lookup and return the correct label for a value.
>
> Is there a function for this, or does it require programming
```
 0
DukeCarey (494)
11/16/2005 11:52:04 AM

Similar Artilces:

LEN returning strange result
Hi, I getting an unexpected result when using the following formula, I'm receiving #VALUE when the formula in J5 returns nothing (the formula in J5 uses "" double quotes to stop an error being shown). =IF(LEN(J5>0),J5/2*145,"") What formula would return nothing i.e. the "" double quotes would come into play. Thanks, Rob Watch your parentheses: =IF(LEN(J5)>0,J5/2*145,"") I'd do this, too, but it doesn't matter to excel: =IF(LEN(J5)>0,(J5/2)*145,"") I just find it less confusing. Rob wrote: > > Hi, > ...

What formula would give a value of a last negative value in a range?
Hello What formula would give a value of a last negative value in a range? I want to calculate a pay-back period from a cumulative cash-flow or a cash-flow. Hi one way: use the following array formula (entered with CTRL+SHIFT+ENTER) =INDEX(A1:A100,MAX(IF(A1:A100<0,ROW(A1:A100)))) >-----Original Message----- >Hello >What formula would give a value of a last negative value in a range? I want >to calculate a pay-back period from a cumulative cash- flow or a cash-flow. > > >. > Excellent, thank you so much, Frank hglamy "Frank Kabel" <frank.kabel@fr...

Sort by Large, Offset from Match, Duplicate values problem
Heya all, I have a large list of dynamic data, which I am organizing in different ways. I realize this can all be done in PivotTables, but we have to do this the olde fashioned way. The table I am drawing the info from looks roughly like this: Worksheet: DATA Player Buy-In Cash-Out Difference # Times entered Joe SHMOE \$930 \$900 \$30 1 Sam DOG \$0 \$0 \$0 0 Peter BLAH \$100 \$1000 \$900 22 Doug FINN \$500 \$0 \$500 22 I am using =LARGE, =SMALL on the columns to sort the data, and then MATCH...

Named range row/column reference #2
thanks. understood, and that does work well. I would still like to be able to reference within named ranges usin offsets from the beginning of the named range. Once I have the offset they would serve as references into other named ranges of a simila dimension. *Is there a way to get vStudentNbr and vTestWeek as relative reference to the beginning of their respective named ranges? * (yes I am a VBA newbie!! and I am not yet writing the most efficien code LOL :) -- Urklnm ----------------------------------------------------------------------- Urklnme's Profile: http://www.e...

up down bar value (difference)
Hiya, I have a line chart with two lines. I have included down bars from the upper line. I would like the difference of value between the two lines (i.e. the height of the down bars) to be shown within the down bars. Is this possible? Thanks, Basil one way to do it, (If I understand what you want) on the spread sheet calculate the difference in say D1:D4 on the chart start a text box and in the formula bar enter = and point to the cell with the difference. format the text box for the alignment and fill colors you want and place it on the down bar for each data point pair. "...

Can this be shown in the Internet Headers of Exch 5.5 Server clients ? If so how can it be enabled ? When you say Exch 5.5 server clients, you mean Outlook? View > Options will display this header. -tom ---- "RichieRich" <anonymous@discussions.microsoft.com> wrote in message news:D6BB585F-805C-456C-BB64-68D5F3B1ECA4@microsoft.com... > Can this be shown in the Internet Headers of Exch 5.5 Server clients ? If so how can it be enabled ? That information is inserted by sending side (it's mail client), Exchange does not control that. RichieRich wrote: > Yes it...

Sum values that precede todays date
I am trying to find a way to sum the values of payments made to date? eg Date Payment 01/01/2007 10 01/02/2007 12 01/03/2007 11 01/04/2007 10 01/05/2007 10 01/06/2007 23 01/07/2007 10 01/08/2007 10 01/09/2007 10 01/10/2007 10 SUM 116 So if it was today it would sum all values preceding the figure for July? Is there an easy way to do this using a formula? I have tried using the offset and match formula but then i can't get it to sum all previous payments. Many thanks for your help Jody =SUMIF(A:A,"<="&TODAY(),B:B) -- HTH Bob (the...

insert all column(x) values in a single textbox
Hi to everyone, does anyone to know how can I insert in an unbounded texbox, separated from a ";" ALL the values coming from the column(x) of a combobox? On my form I have a combobox with its row source to a multicolumn query and it's bounded with an ID field. What I have to be able to do is that: to insert for each row, with same ID, of the combo the value with respect to its (x) column. Hi hope to have been clearly... Many thanks, Stefano. On 13 Feb, 08:08, ricc...@tin.it wrote: > Hi to everyone, > does anyone to know how can I insert in an unbounded texbox, separated &...

how to add a cell value to a letter to reference another cell
Is it possible to take the value of a cell (say the value of 83 that's in cell A1) and append it to a letter (Z) so that a subsequent formula can get a value that's in Z83. Hope this make's sense. Thanks in advance for the help! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ One way, =INDIRECT("Z"&A1) -- Regards, Peo Sjoblom "Mac Macaroo" <Mac.Macaroo.vo1rn@excelforum-nospam.com> wrote in message news:Mac.Macaroo.vo1rn...

clearing values of cells in named range(s) so the cells are empty
Hi all I am working on project where i paste data-set into a worksheet (sheet1) , do several calculations and put the results specific cells on sheet2. I am using named ranges for the cells in sheet2 that receive the data. WHat i am trying to figure out is a way to set the cells in the named ranges on sheet2 to 0 or "" before starting the routine that does all the calculaions. This way I can be sure that the data on sheet2 belongs to current set of calculations. Thank you in advance for any advise. BRC Assuming one of the named ranges is name SomeRangeName, then this state...

Changing formula to value/result
Hi experts, How do I go about changing the cell formula so that it becomes the result of the formula. Thank you! -- sutats ------------------------------------------------------------------------ sutats's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24565 View this thread: http://www.excelforum.com/showthread.php?threadid=387125 Here are 2 ways to convert formulas to their values: For 1 cell: Select the cell, then... Press [F2] (to edit) Press [F9] (to convert to the value) Press [Enter] (to commmit the value) Or (for multiple cells) Select the cells, ...

Allow for user-defined labels to be changed in Customer Addresses
In Customer Address Maintenance, I would like to change the labels for the user defined fields using a setup and not using modifier. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default....

VBA sort range with certain fontcolor
Hi all, Ik column K there will be a continuous range of which all cells have a certain fontcolor, the font color code is, say, 5. The range can be 1 cell up to 30 (or so), perhaps even zero. There are no cells in column K outside that range that also have font color code 5. While executing the code it is not (yet) known what the rownumbers of the first and last cells in that range are. I need code that will sort (descending) the following range: from row of first cell (in column K) with font color code 5 and column A to row of last cell (in column K) with font color code 5...

How to locate differences between two sheets/ranges?
Well, here's my problem: each day here at work I receive a file with updated info on specific data. Form is always the same -specific number of columns with the same type of data, only the number of rows may change. However, every day I have to go through the data to see what has been changed and/or what has been added/dropped. Is there any way to do this easily? Kind regards Th Hi Have a look here for some ideas. I'm sure one of them will suit: http://www.cpearson.com/excel/duplicat.htm -- Andy. "Gryzor" <Gryzor@discussions.microsoft.com> wrote in message news:...

Hide Rows or Columns with +/- button above row/column label headin
Hi, on some excel files, I have seen a +/- button located between the toolbar and the cell label headings. The button hides/reveals rows or columns. How do I add this button for rows or columns of my choice? Thanks for your help. Data>Group and Outline. Gord Dibben MS Excel MVP On Wed, 18 Nov 2009 14:16:01 -0800, WA <WA@discussions.microsoft.com> wrote: >Hi, > >on some excel files, I have seen a +/- button located between the toolbar >and the cell label headings. The button hides/reveals rows or columns. How do >I add this button for ...

Click Image and call label Click event
I have a menu form with labels and images. I have highlight and unHighlight code functioning fine (switching images, fonts, etc). All of my labels have _click() events that are currently the standard Private Sub cmdName_click() events that all work correctly. I created a function Function ImageClick (cmdName as string). For each Image, the Click event calls the function =ImageClick("cmdName"). So, when you click the image, it sends the related cmdName and should run its click event. I cannot get it to work. Here is an example of one image and label scenario. Label nam...

formula to return value from a matrix
hi Filtering is not an option :) I have a table that shows skill titles across the columns e3:cz3 and staff names down c4:c103, for each name and skill there will be a value between 1 & 4 see example below C E Name Excel Colin 3 I need to insert the finding into a bespoke report that if I select a skill (from a drop down list) the report will list the names and associated value Skill title drop down is k5 name is k8, skill value is j8 Cheers in advance Indicatively, perhaps a basic Index(area, match(row),match(col)) ...

numeric values
Hello guys I have 2 tables with exactly the same fields and the same number of records. Every day those 2 tables (updated) have to match. How can I proceed to ensure myself that there is no difference? When defining a numeric key, it appears that there are some differences, even as both records appear to be equal. Can someone tell how to proceed when I want to maintain this numeric value as a valuable criteria to investigate if there are any differences? Kind regards? There is seldom a good reason to have the same data in two different tables. But assuming that you have one, here goes. Fi...

My table data 5/1 5/2 Customer A Qty1 10 10 Qty2 1 2 ------------------------------------------ Balance 9 17 Customer B Qty1 10 10 Qty2 5 2 ---------------------------------------------- Balance 5 13 I am trying to copy and paste a balance formula for each customer group. The formula is basically Qty1 (Previous cell) - Qty2(Previous cell) ...

setting value for combo boxes
i have a combo box bound to a table that when 'not in list' opens a form to add a new entry to the bound table. once the user adds an entry to the bound table via the newly opened form, i'd like this value to populate the original form control, which is still open. and not to get to greedy, but i was hoping it could be global in that i'd like one code or macro so i don't have to do it for every combo box in the form. any help is appreciated thanks lefty On Feb 23, 8:52 pm, "lefty" <richardpo...@gmail.com> wrote: > i have a combo box bound to a table th...

Enter "Parameter Value"
AccessXP on WinXP Attempting to update a series of records from a LINKED table. The following SQL statement results from the design view settings to replace classnumber field in the datainport table with the ClassID filed contents in teh linked table tblClasses. This returns a Enter PArameter VAlue dialog box. I am accustomed to getting this dialog when I have misspelled a variable name, but I used the list of tables and fields to verify that I have the correct variable names. What suggestions can you offer to the sql below? UPDATE dataimport1 SET dataimport1.[Class Number] = ([tblCl...

Can you change the length of an excel chart data label?
The label is cutting off the number and I will need to change the length or scrap the label all together if the functionality is not available. Thanks for your help! A know bug. Some people have this problem other do not. Workaround: add some dots to the end of the title then format the dots to be invisible (in case others view the chart and do not have the 'bug') by making the font colour match the label's background colour (generally white) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Alia" <Alia@discussions.microsoft.c...

how do I attach files but not within the document
I want to atach severeal files to a report. I don't want them within the report but want to reference them so the reader can see them. Any help? Have you considered using hyperlinks? -- Stefan Blom Microsoft Word MVP "DLW" <DLW@discussions.microsoft.com> wrote in message news:1F22E824-3BB2-437E-BC30-E08983478E9C@microsoft.com... >I want to atach severeal files to a report. I don't want them within the > report but want to reference them so the reader can see them. Any help? ...

convert formula to its value
I have column made up of formulas that I want to convert the formula to it's value. Is this an option in Excel Copy the range, then edit, paste special, values. Robin Hammond www.enhanceddatasystems.com "Andrea" <anonymous@discussions.microsoft.com> wrote in message news:08fe01c50734\$387b84c0\$7d02280a@phx.gbl... >I have column made up of formulas that I want to convert > the formula to it's value. Is this an option in Excel Yes, Highlight the range of cell, select Copy. From the Edit menu, select Paste Special. Choose Values. -- Rob van Gelder - htt...

Need to make a drop down list of 3 items that input a value in ano
I have a savings calculator that i am working on. we have three products that i would like the user to be able to select from. basis their selection from the drop down list, i would like a corresponding value to go into a certain cell. how do i do this??? To create the dropdown list: - select the cell where you want it to appear - select Validation from the Data menu - choose List - you can either type in the list of items, separated by commas, or use the grid button next to the textbox to select a range of cells that has the list of products - click OK To populate the other c...