I am currently using this formula to total the items in Column AH of my worksheet, which usually begins on row 19 (Occasionally on row 20, which corrupts my formula). Since the numbers are always below the header, entitled "Exception Amount" I am wanting to know if I can combine these to formulas into one that will correct the occasional anomaly.
This was a snap in Lotus. Unfortunately, the VLOOKUP and HLOOKUP formulas don't translate to Excel on conversion. I need to estimate the weight of reinforcing steel in concrete based on 7 different sizes of rebar and spacing of the bar between 1" and 24".
I have created a matrix(lookup table) with the bar spacing listed in decimal equivilents in feet on the top row.Inthe row below, I have numbered the columns from 1 to 24. the next row is blank. Along the left side of the matrix, I have listed the bar sizes 3 thru 10. Within the table I have listed all the bar weights/SF of concrete. IE if the slab has 1 mat of #5 rebar spaced 6" oc. If you look in row 7, under column 6, the weight of weight of the bar in that sf of concrete is found. In the part of the worksheet where the caculations are performed set up as follows:.....
using the table above i am trying to do a three match to return a value. I am using the following formula and keep getting a #value error. essentially the formula is returning "Green"*2354. What I am wanting to return is the value 2354.
I have a table full of names and cars sold. The table tracks the person's progress. It is like a diary. It's a simplied version of the DB I am currently working with.
Is it possible to do the following things? I have basically 2 types of questions.
1)When was the last red car sold?
2)When was a car sold irrespective of color?
The answer to each question will be put in separate columns. I have only included January but I will do this for the entire year of 2010 and also I will do this for all of the names in my database. I think there are about 300.
When was the last time ANDY sold a RED car? The answer should be 1/12/2010 The answer will be put in ROW A COLUMN I
When was the last time ANDY sold a car irrespective of color? The answer should be 1/12/2010 The answer will be put in ROW A COLUMN J
When was the last time BILL sold a RED car? The answer should be 1/10/2010. The answer will be put in ROW B COLUMN I
When was the last time BILL sold a car irrespective of color The answer should be 1/11/2010 The answer will be put in ROW B COLUMN J
I think I need to combine functions to allow me to automate data entry from a table from another worksheet. If the Name entered in column A on Exp Breakdown matches a name in column A of Salary Breakdown I cannot seem to write a function to scan the table in Salary breakdown and lookup the percentage that person is paid from a specific program number. I have attached a sample worksheet to hopefully make it easier to understand.
i'm trying to use HLookup to find an adjusted midterm grade that's given. but i have some conditions:
If student missed exam and has a zero – keep zero. If student has a grade of 1-119 points, increase their grade 40 points. If student has a grade of 120-125 points, increase their grade 35 points. If student has a grade of 126-131 points, increase their grade 31 points. If student has a grade of 132-139 points, increase their grade 27 points.
with these conditions, if my midterms grade is 120, how would i calculate it using HLookup? i worked on it but i keep getting the #NA! error. =H4+HLOOKUP(H4,B24:D25,2).
I want to use a value in one cell as a row designation, and a value in another cell as a column designation. Ultimately, the values will be text which will refer to row and column headers. What formula would allow me to do this? example:
A1 contains B B1 contains 2 B2 contains "tribbles"
On my worksheet 1, I have two dropdown boxes: B7 (that gives the building name) and B11 which gives the Type of Commission (Renewal, Expansion, New). B 20 is supposed to automatically select the percentage based upon what B7 and B11 select. E.g. B7 = House, B11 = Expansion then B20 should be 3.
This is my formula. =HLOOKUP(B7,'Building Details'!$A$2:$R$24,MATCH(B11,'Building Details'!$A$2:$R$2,0))
I am facing a problem using Hlookup function. I am using Hlookup to do a control check of consolidation i am doing here, however it only returns the first found value instead of all correct values (or range).
I am attaching a file with an example. In the file formula is used in Supply(S) sheet on row 73.
I have various 6 x 6 blocks which contain just numbers, in another part of the spread sheet I have a 1 x 6 block of numbers. What I need to do is to check whether any of the numbers in the 1 x 6 block appear in any of the 6 x 6 blocks.If that does occur then I want to make a specific cell increase by the times that the match happens.using HLookup but just got errors and the If Statement, although did work, went on and on and on.
Im using the following HLOOKUP formula : =HLOOKUP($B$14,$DA$15:$EH$380,2,FALSE) and I need to drag it down 365 times, but the row index num, ie ‘2’ does not increment with the drag. Is this correct – or is there a way of making the 2 increment?
I have some experience with programming, so I understand the theory behing things like loops, arrays and functions etc...Only problem is, I need to make a program at work to ease workloads and I'm afraid I have no idea where to begin.
Basically, I need something similar to a lookup, but thanks to the limit of IF statements available in the formula bar, I can't write what I need.
The program I need to write wouldbe similar to an autofill function. On Sheet1 of my spreadsheet, there are fields: Name, Address 1, Address 2, Address 3, Postcode, Area, Rep.... (The last three are the most important) On sheet two, Postcode, Area, Rep already have data entered in thier respective columns. (D3, E3, F3).
What I need done on Sheet1 is when the user enters the first two characters of a postcode, the 'program' will lookup the entire row respective to the postcode that is entered. when enter is pressed, the Postcode will copy itself from the other sheet as well as the Area number and the Rep into the empty fields in Sheet1. The other way to do this is look through a long list until the right postcode for an Area is found then assign the Rep the Contact details (Add. 1, Add. 2, Add. 3...).
I have a huge data and I need to find values through HLookup and than have to find the sum of that whole column.But when I tried to to so It is showing error as Formula broken.
The formula I'm looking for should look for the name mentioned in B2 in range AC2:AF400 and retrieve the corresponding data below it starting from D11 to D41 and continue on F7 to F34 etc.
I have a formula that looks like this: =HLOOKUP('Output'!$B$3,'Input'!$B$1:$P$300,'Input'!$A3+1,FALSE)
It is working very fine, when the cell has a value, but when it doesn't - it will return 0. So my question is: is there any way to make it return certain value or word like "No value" instead of zero?
I have an Excel workbook containing a number of worksheets. My problem is that on one sheet(BlkPick) I have Rows containing headings(Location Names)and a column containing Product names. I wish to lookup the corresponding value contained at the matching intersection on another worksheet. I have searched this forum along with google and found that INDEX & MATCH are what I should be combining with Vlookup and these work when used on the same worksheet but when I test using say MATCH alone I get an error when attempting to MATCH my result from a different worksheet.
I have a chart of numbers pertaining to progress of a project by month. The end of month dates are in a row across the top, the progress % are in corresponding rows and columns below:
I'm trying to do a SUM of HLOOKUP values to get a total progress for a given month. For example, I need to see the total progress for end of May from end of March: SUM(HLOOKUP(5.5) to HLOOKUP(1.2))=5.5+3.4+1.2 in a cell at the end of the row. How can I do this? Do I need to use another function?
Really basic question - I'm trying to get HLOOKUP to lookup a number and return text that is in another cell but it keeps returning #N/A. I know the formula works because when I change the text (that I want returned) into a number it returns the figure. How can I get round this?
Is the Hlookup funktion not included in Excel 2002? I've been trying to run basic exemples from microsoft, and ones made by myself, and I just can't get it to work. Excel just keeps telling me that's it wrong in the formula. I really need hlookup work.
Is it possible to use HLOOKUP without exact matches. e.g. < =HLOOKUP(E1,Actuals,2) > where E1 is "2009 October" and the lookup value in named range "Actuals" would be simply "Oct".
I am using a Hlookup to retrieve Words and Figures but when retrieve words it is failed and output is figures ONLY while I need the same word to be shown in the cell value?
