Multiple lookup values in =HLOOKUP

A classical formula might be =HLOOKUP(a1,a100:z100,1,FALSE). But I want to 
display the result if the lookup value = a1 or a2 or a3 or...a10 ie: anything 
in the range a1.a10, without writing a multiple nested statement. Any ideas? 
Thanks.
-- 
Peter
London, UK
0
Peter2031 (452)
9/17/2005 3:40:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
384 Views

Similar Articles

[PageSpeed] 19

Try...

=INDEX(A1:A10,MATCH(TRUE,INDEX(COUNTIF(A100:Z100,A1:A10)>0,0),0))

....confirmed with just ENTER, or...

=INDEX(A1:A10,MATCH(TRUE,COUNTIF(A100:Z100,A1:A10)>0,0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <CE55088A-5E9F-4C94-8D91-4F4AD66E63A7@microsoft.com>,
 "Peter" <Peter@discussions.microsoft.com> wrote:

> A classical formula might be =HLOOKUP(a1,a100:z100,1,FALSE). But I want to 
> display the result if the lookup value = a1 or a2 or a3 or...a10 ie: anything 
> in the range a1.a10, without writing a multiple nested statement. Any ideas? 
> Thanks.
0
domenic22 (716)
9/17/2005 7:38:48 PM
Reply:

Similar Artilces:

How can I minimalize the difference between extreme values on a c.
I'm making a chart with two values for class. One of the values is very small (3) and the other is very large (317). How can I make the axis values "break" so to speak, so that the difference between them is minimalized. For example, I need a portion that goes from 1-7 and another that goes from 300-700. I can't put them on different charts. I describe one technique on this page, and include links to others: http://peltiertech.com/Excel/Charts/BrokenYAxis.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutio...

excel 97 vba
cell C19 = product code drop down list cells N1:O449 = description of prod associated w/ dropdown list cell E19 = product description column cell I19 = price column cell P1:Q449 = list associated with product code drop down list Right now there is this code in cell E19: IF(C19<>""(C19,$N$1:$O$449,2,0),"") This code allows me to pick a code # from the drop down list in C1 (which is referenced to column N and O) and once selected, th corresponding product will appear in the product description cel (E19). I now am trying to set up a code in cell I19 so that once th...

Pie Chart
Good day, I have data organised as follows. Col A Col B (Header) ( % ) red 39% blue 0% white 24 % Green 10% Brown 0% My problem is that when i create a pie chart, it picks up the 0 valuses as well, and so the chart looks clustured, as i need to have the lables. Please advise how to tell the chart to omit 0 values. One option which i cannot use due to layout of the report is Hiding rows and then plotting only visible cells. Thanks Best regards Good Day, Jon, Thanks for the answer, but what i need is for the chart not to plot 0 values. SO that there are not too many slices of the pie....

Total a column from sheet 2 based on value in sheet 1
Col B Col C Col F Mary Team 1 $331.00 George Team 1 $222.00 Sam Team 2 $186.00 Tom Team 2 $100.00 Above is an example of my data on Sheet 2. On Sheet 1, I want to total all the total funds raised per Team shown on Sheet 2. I am trying to create a summary of what each team raised. I tried using the formula: =SUM(('sheet 2'!F2:F482=Sheet1!B2)) and I get just a dash in my total col. Can anyone provide some help? Thanks Look in HELP for the SUMIF() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Nee...

Add Value to the Chart
I have a chart done from the table shown below. However, I want to put the value (date) in addition of the values (X) and (Y) axis. So far, I have been doing it manually. I wonder if there is any way to it automatically. Thanks in advance. Maperalia. Date "X" "Y" 6/21/2005 0 0 7/6/2005 147 0.008 7/18/2005 197 0.012 8/5/2005 255 0.016 8/18/2005 289 0.016 9/8/2005 337 0.006 9/19/2005 360 0.017 How would you like the date to appear? As a data label on each point? then use one of these handy data labeling add-ins: Rob Bovey's Chart ...

constant values
I have a very simple question, which I am not able to resolve though. I have created a query, which combines records from two tables. Works nicely. Now I would need to add several columns with either a) fix values, which will never change b) values which apply for all records, but which I would like to enter within a form every time I would like to run the query. The background is that I will use access as a mapping tool, so an original file will be uploaded and result in a table with the same name always. The query now takes the mappings for another table and combines the ...

Vlookup not seeing all values
Problem linking values from one wookbook / sheet to anouther. Almost all cells work fine but some do not, they do however if I copy/paste the lookup value into a "search all sheets" and double click the cell in the source sheet that contains the lookup value. Example: Look up cell for destination file on sheet1 = 1234 Look up cell from source file on sheet2 = 1234 Both are formated as "text" but the vlookup data is not transfered until I double click on the cell containing 1234 in the source file sheet2 then it works fine until I update the file aga...

Lookup function 02-14-10
I have excel 2003. I did a vlookup for my transcripts page. In the first vlookup i needed it to look for the letter grade and give me the gradepoint average. Now in the other cell i need it to look for the grade. In the cell that gives the gradepoint average it has a formula in their already. Can i still do a vlookup if the cell that has the information has a formula in it already. Example. The cell that has the formula adds all the grades from each class and gives me an accumluated gradepoint average for the ten classes taken. Now that gradepoint average I want a letter ...

Conditional Formatting Across Multiple Cells
I have data file that I want to conditional format Red/Green fill based on >= conditional on a single row of data. In Excel 2003 it was fairly simple to lock a row but not a column and by selecting all the cells to format it would change the column relative to the cell. Just having an issue transitioning to conditional formatting in 2007. Sample Data a b c d 1 goal 10 245 125 2 day1 8 200 76 3 day2 8 250 125 4 day3 15 250 130 5 day4 15 300 150 6 day5 0 100 0 7 Avg 9 220 96 Cells Rows 2 - 7 should be conditional on Row ...

Hover values in chart
I have a line chart with a secondary vertical axis with values displayed on the right of the chart. When I hover over this secondary data line I get a message box that gives me the "series", "point" and "value". The "point" however is a number depending on how far you are from the left side of the chart. If I hover over the primary data line the "point" displays the value on the x-axis. There doesn't seem to be a way to specify the data sequence for the secondary x-axis. How do I get the secondary data line to show the "point" va...

"implant" ActiveX ComboBox value into if eq'n
Morning all. Ok, I've got my combox active to where I can read the names in the list. I'd now like to have an if equation that will pull the value I select in the combobox. Do I have to write a secondary macro/function for that, or can I do it directly on the worksheet? Private Sub ComboBox1_Change() Select Case ComboBox1.Text Case "Item1" MsgBox ("Item1") Case "Item2" MsgBox ("Item2") Case "Item3" MsgBox ("Item3") Case "Item4" MsgBox ("It...

Sum values over range of dates
Need Help!!!! PLEASE! Here is the example... If I have a huge table of dates and values for those dates and need to sum based on a given date range, how do I sum it up? date 1 date 2 date 3 date 4 date 5 Sate 1 12 7 8 1 1 State 2 10 4 6 2 8 State 3 5 4 2 3 7 start date end date Sum would be??? Sate 1 2 5 17 State 2 1 3 20 State 3 2 4 9 But what is the formula I can use for this??? How can I set it up so I just need to change start and end dates and it will calculate automatically???? Please help! Thank You!!!! Assume: This data is in the range A1:F4. > date 1 date...

Mapping estimated value to totalamount
Hi, I define a map between opportunity estimated revenue and quote totalamount. I set the estimated revenue to be user provided and write a value by hand. The value is not carried to the totalamount field of the quote when I add a related quote. Instead it is calculated from the amount fields of the quote products. Is the totalamount field always system calculated, cannot we override it with the value in the estimated revenue? Thanks, Bilge Gul "b_gul_t" <bgult@discussions.microsoft.com> wrote in message news:840D5F58-34E8-4639-9A8C-C0508D9C9686@microsoft.com... > ...

Copying number to clipboard, subtracting 398 then pasting the value to overwrite the original
Hi. I am very new to this. I'd be really grateful if someone could help/guide me. I want to create a macro in Microsoft Word but I don't know visual basic. I want to be able to highlight a number then: - copy it to the clipboard - subtract 298 - paste the value to the Word document, overwriting the original text Hi John, There is no need to involve the clipboard if you are only changing the selected number. The following macro subtracts 298 from the selected number. Sub Subtract298() If IsNumeric(Selection.Text) Then Selection.Text = Val(Selecti...

Unprotecting multiple worksheets w/ Macro
I am protecting with a macro, multiple worksheets in a workbook with a macro. I can't get the unprotect of multiple worksheets to work. Does anyone have a working macro to do this. _____________ Tippy Sub UnprotectSheets() Dim sh as Worksheets for each sh in ThisWorkbook.Worksheets if sh.ProtectContents or sh.ProtectScenarios or _ sh.ProtectDrawingObjects then sh.unprotect Password:="ABCD" end if Next End Sub -- Regards, Tom Ogilvy <tippy@att.net> wrote in message news:c7vlpv0sqb9eov641aj31ohnqnps7hvgvm@4ax.com... > I am protecting with a macro,...

Adding values
I have a spreadsheet with one column of names the names are repeated down column :A Colum :D rob 5 rob 2 martin 5 rob 6 martin 5 etc: in another column D I have values of numbers Is it possible to add up all the values in column D associated with Rob i.e. Rob =13 Martin =10 Thanks Rob Look in the help index for SUMIF -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "CrashMatRob" <crashmatrob@ntlworld.com> wrote in message news:uRgOzPhiJHA.3708@TK2MSFTNGP04.phx.gb...

Null Value
When I try to enter a date on a form I keep getting the following error message; "You tried to assign the Null value to a variable that is not a Variant data type (Error 3162). The help for this error recommends declaring the variable. The only problem is I do not have any modules created for the form. What can I do to stop this error message from happening? The form has a code module. Perhaps you mean you have not created any code in the form's code module? If you used a wizard to create, say, a command button there will probably be code in the form's module, so ...

Calculating Between Values
Hope you can help me! I have a column on one spreadsheet called "Platts Ports" Now, I want to analize column E. I want to be able to pick out all the values between 0 & 0 (>0 &<2). I then want to say the following: Where value = between 0-2 (COUNT) then add up column F where all these fields apply. Then divide COUNT(E) by the total of F( the sum we just calculated) To top this off, I need to do this from another sheet (within the same Spreadsheet) Hope you understand. Email me at RBotley@Gmail.com if you need me to priovide more info! Thanks in advance -- ...

Rounding up Time Values
Hi I am using the formula below to round time values up,in this case t the next five minute increment =(TIME(HOUR(BB14),CEILING(MINUTE(BB14),BB13),0)) BB13 = 5 BB14 = 16:00:02 This formula only rounds up to 16:05:02 if the time is 16:01:00 o greater,i would prefer it if the formula could make the time round u as soon as one second has passed, 16:00:01 and not when one minute ha passed. Can this be done Ad -- Message posted from http://www.ExcelForum.com What is in BB14? You can just use =CEILING(BB14,5/1440) will do what you want Or if there are dates as well you can just format...

Line Graph Excluding 0 Values
Dear, I have plotted a line graph consisting the data for the entire month. The data range is getting calculated automatically by a formula. 1 Dec - 100 2 Dec - 105 3 Dec - 0 4 Dec - 0 So on... The data for the days starting from 3 Dec is zero and the line graph suddenly goes down to zero... Is there any way that the line graph picks the data excluding the zero values? I mean the line graph should only take the data for 1 Dec and 2 Dec without changing the data range. Thank you. one way might be to hide columns for 3 Dec, 4 Dec etc. On 2 Gru, 08:19, Sasikiran ...

IF formula to round up values depending upon their outcome
I am using an IF formula to calculate between two cells, one is J (width) the other is K (length). Currently these formulas give an answer that then has to be rounded up based on the decimal place. I need the formula to also round up the amount to quarter increments. For example if the answer is 1.17 then the formula needs to make it 1.25, if it is 1.33 then the formula needs to make it 1.5, and finally if it is 1.63 then the formula needs to make it 1.75. So how do I add or make the formula round up to quarter increments? The formula that I am using is: =IF(J41<=3,K41/4,IF(J4...

Merging Workbook Table data Based upon Value comparisons
I have two workbook tables (Two different workbooks) with two matching column names. What I wish to do is to merge values from one table to another, but ONLY for those records inwhich these two columns have matching values. Would this be possible? Jay Are you saying you have two workbooks, or are the tables within one workbook? "jayceejay" <jayceejay@discussions.microsoft.com> wrote in message news:AC73B2C7-83EF-4D27-A464-32AEEE7D4214@microsoft.com... >I have two workbook tables (Two different workbooks) with two matching >column > names. What I wish to do ...

Reference a cell value in a formula
I have the following formula: =SUMPRODUCT((MONTH(Summary!$F$7:$F$15000)=1)*1) I want the "15000" to be replaced by the varaible value of a cell in an other worksheet ("Summary"). I tried using INDIRECT but it didn't work. Thanks in advance =SUMPRODUCT((MONTH(OFFSET(Summary!$E$7,0,0,C4,1))=1)*1) C4 in this case is the cell that holds the number of items to be used in the calculation -- If the post is helpful, please consider donating something to an animal charity on my behalf ..... and click Yes "Leon" wrote: > I have the f...

send attachment to multiple recipients, but viewable only to one
Hi there... Possibly a bit of an odd request... I want to know if it is possible to send an attachment in an email to a number of people, but to specify that only one of the recipients would be able to open it... Basically I want to be able to demonstrate that an attachment has been sent, but not have it read by everyone it has been sent to. I'm using Outlook 2003... Any help would be very greatfully recieved! Thanks in advance... Chris Not sure if this is what you're looking for... One option would be to set the file with a password to open and send the password only to the ind...

Lookup / Array Range / find the value
I have seen similiar examples but not that were not using exact matches to retrieve data. This seems simple and maybe I am over thinking but I have been unable to crack this nut. I have a table where the first tier sales runs between 0 and 20k and awards 1K commission. I want to be able to enter the salse result for various team members and obtain the correct commission amount based on their sales for the period. How should I complete the commission formula for this process? Sales Value Start Sales Value Stop Commission $0 $20,000 $1,000.00 $...