Lookup Row & Column Heading To Return Corresponding Header
I have a list of names and the chores they need to do on a certain day. I need a formula that returns the chore the person needs to do when the date is filled in.
Ex: Sheet2 Col:A has names filled in A2:11 , row B1:J1 has dates that you would input.
formula goes in B2:J11
Sheet1 is the master with all the data the formula would be pulling from.
column A2:A11 has the names , row B1:S1 has the chore that needs to be done.
B2:S11 has the dates already filled in.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Lookup Value In Header Row And Return
I have a worksheet with the following values: header row 15 17 19 25 etc. each column underneath the row will have a value of 0 or -1 A B C D E etc 1 15 17 19 25 2 0 -1 -1 0 3 -1 0 0 0 4 0 0 0 0 etc I want a formula that I would put in each row that would return the value of Row 1 if there is a -1 below that value. So, in this example: Row 2 formula would return 17,19 Row 3 formula would return 15 Row 4 formula would return nothing
View Replies!
View Related
Lookup Row In Column & Heading In Row
I'm trying to enter info in a cell based on what is entered in two other cells. So I have a dropdown box for the species on another sheet as well as a dropdown box for rail sizes. Then I have to crossreference them to come up with an upcharge number. Sorry for my basic lingo. I hope it's understandable.
View Replies!
View Related
Lookup On Column And Row Header
I need a formula to lookup and retrieve data from a table of values. Given an EMP-ID in column A of the row that contains the formula and given a column name in row 1 of the column that contains the formula, I need to do a lookup. So, in effect, I am doing a lookup based on two values .. the column header and the row header. I have attached a small model to illustrate what I need. Cell C-3 in the first worksheet will contain the formula to lookup and retrieve the value in cell C-7 in the second worksheet, based on the values in cell C-1 (column header = “Database Col 2”) and in cell A-3 (row header = “257”) in the first worksheet.
View Replies!
View Related
Lookup Column Header Value When First Cell Is Populated In A Row
i have a manpower chart with months along the top and names down the far left. People are going to start work in different months so i want to know at the side of each persons name the month they start. Does that make sense? Heres a simple example I have column headers starting at B1 thru F1, with the following values Month1,Month2,Month3,Month4,Month5 In cell D2 i have the value 1 I want Cell A2 to return the value Month3 Whats the formula i need to input in A2
View Replies!
View Related
Find Value In Table & Return Column / Row Header
I am having trouble with getting a value (which is the row and column value) from a set of numbers. I have attached an example table to try and make things clearer. What I have is a table with a set of Row Headers, and Columns Headers all numeric. e.g Columns headed 1, 2, 3, 4, Rows 30, 40, 50 etc. I want to find the 1st occurrence of a value e.g. 1.0 in the table (by 1st Up mean closest to top left), and then give me the value of the Row and Column Header that corresponds to that. So for the example attached, I want to get the row Value 200 and Column 6 from finding the 1st occurrence of the number 1. I have tried various combinations of index and Match etc. But I can't get my head around the fact that the position of the 1st no. 1.0 can change, therefore I can't use any releative positions etc.
View Replies!
View Related
Lookup Value Then Return Value In Row 1 Of That Column
I have a list of values in column A (part numbers) In columns B to E I have lists of similar values (part numbers, with row 1 representing their location e.g warehouse, office, etc). In column F2 I would like to search for the value in A2 in the range B2:E100 and return the value in B1:E1 if the value appears... if it doesnt appear in any of the columns I would like 'not located' to appear in F2. I've been trying to do this for a while but am making no progress at all.
View Replies!
View Related
Find Value In Row, Then Return Heading Above It
I have a 10x10 table. First row contains column titles (plain text, hardcoded). Each line below them has a 1 in *just one* cell. So, each line contains 9 zeros and one 1. In the 11th column I want to check which column has the 1 in it and return the column's title. So, if the 5th row has a 1 in cell E5 I should get, in K5, the value of E1 (the title of the E column that is).
View Replies!
View Related
Return Column Number Of Heading
I have range like (3E:3K) in this range are only 2 different expressions. "Result and " forecast". I wanna look from left to right for the first cell with the string "forecast". And than I want to work with the column of this cell, only this column. How do i do that? Edited I corrected the coordinates. I want to look in a specific column line (Nr.3) from there the first Cell with "Forecast" and from that String the row number. They following 4 posts were based on wrong informations by me.
View Replies!
View Related
Return Heading Of Column With Maximum Value
In attached example, I have 3 columns of fractions with a final row of sumtotals (taken from elsewhere). I test to see which column has the maximum value in each row and allocate the result with the column's heading. Sometimes, two or more columns have the same max value. In this scenario, I wish to select the column heading that has the largest sumtotal.
View Replies!
View Related
Find X In The Corresponding Row And Return The Header Value
I need a formula to put into cell B19 to Find X in the corresponding row and to return the header value ******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows .NET Server (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEF1Part NumberDescriptionSubframe BuildCut And ChopHopper BuildCabBuild2026319Assy Air Ram Auto Blanking X 3026321Assy Air Ram Rear Boom L/H X 4026320Assy Air Ram Rear Boom R/H X 5022587Assy Air Ram Side BrushX 6031159Assy Air Ram Side Brush Large DiameterX 7030501Assy Blanking Flap Valve Single Vm Minor 8034783Assy Brake Pedal Lh Vm Minor Fl X9034784Assy Brake Pedal Rh Vm Minor FlX 10036349Assy Brush Control D/S No Hp Opt Mer/Mag X 11024553Assy Brush Core 1300 Large Widesweep X 12024554Assy Brush Core 1300 Small Widesweep X 13023169Assy Brush Core Large 7000 X 14025662Assy Brush Core Large 80/85 X 15032482Assy Brush Core Magnum Poly/Wire Mix X 16 17 18 19034784Subframe Build 20023169Cut And Chop Sheet1 [HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Return Column Header Of First Column That Doesn't Contain 0
I'm looking for a formula that will return the column header (a date) of the first instance of a number greater than 0. For example if columns A through S had dates as in row 1 (header row), and in row 2, every column contained a 0 up until column P, I would want the date (Row 1, Column P) returned? I think this might be a sort of index/match formula, but I am not very familiar with these.
View Replies!
View Related
Lookup Column Header Based On Cell Value
I have a spreadsheet an excerpt of which is as follows: K L M N 1 Doors Security Vehicle Key 2 TRUE FALSE FALSE FALSE 3 FALSE TRUE TRUE FALSE 4 FALSE FALSE FALSE FALSE Want I want to happen is to return the column header where there for a cell which contains "TRUE" in a blank column. If there isn't a cell with the value "TRUE" then to return "N/A". In the above example in blank column P for Row 2 would be "Doors", Row 3 would be "Security,Vehicle" and Row 4 would be "N/A". In my actual spreadsheet I have 12 columns (L to W) which have the TRUE or FALSE values. I know it has something to do with either HLOOKUP, INDEX and MATCH but can't seem to get it right. Any ideas?
View Replies!
View Related
Return Column Header Of Last Positive Value In Range
I've got numerical data in columns A through AF, starting in row 4. I need a formula, starting in AG4 that I can drag down. I need the formula to look back across the row until it gets to the last posistive number, then return the column header of that column (the column headers are in row 3) so for example Header row Jan Feb Mar Apr May Jun Jul Aug Data 0 3 0 0 8 0 0 0 the formula would return May in this example as 8 is the las positive value and the column header for that column is 8. There are no blank cells in the data.
View Replies!
View Related
Column Header Of Specific Value In 2nd Row
Have a set of dates as column headers across a worksheet. Rows are product names. When a product is delivered, the cell is marked "delivered" for the date (in the column header) it was delivered. Need a formula that will look across each row (product), and return the date of the first cell with the value "delivered". I have: =INDEX(F19:AS19,1,MATCH("delivered",F19:AS19,0)) <for row 19. right now, but its just giving me "delivered" as the value and I cant figure out why.
View Replies!
View Related
Looking Up Column Header Value Based On Color And Position In Row
I am trying to create a schedule using colors. The colors will indicate the start time, break time, lunch time, break time (second break) and end time. see attached document. Thank you for attempting to solve this puzzle using formulas and or VBA code. I am trying to automate the schedule so the numerical times automatically appear when I "paint" the schedule.
View Replies!
View Related
Row / Column Header Not Fully Shown In Normal View
Recently I come across a file with only 1 sheet, in which is a table only occupied an array of A1:Q49. In Normal View I can see the row / column header of A-Q and 1-49 as usual, but nothing outside that range (only grey colour). Also, the empty cells outside A1:Q49 are all grey too. How can I do that?
View Replies!
View Related
Set Print Area For Range Bounded By Row & Column Header
I want to select a range in a spreadsheet and then set it as the print area. the last column I want to be selected is contains the text data "ABC" in row 1 and is the only column with data "ABC" in row 1. The last row I want to select contains the text data "DEF" in column 1 and is the only row in column 1 containing the data "DEF". Basically I want to select the range [A1:column("ABC")row("DEF")] and then set it as the print area.
View Replies!
View Related
Row Lookup: Return Zero If No Match
I have exported an income statement report from the ERP system to Excel. Only items that are not equal to zero are populated. So if there has been no expense in the Central division for Consulting fees, it does not show up on the report. Here is the formula:use control shift and enter small(if(row(A1:A100)-ROW(A1)+1>MATCH("Central Division",A1:A100,0),if(A1:A100)="Consulting Fees",ROW(A1:A100)-ROW(A1)+1)),1)( Excel Jeanie HTMLSheet2 A 2 East Division 3 Sales 4 Cost of Sales 5 Payroll 6 Lights 7 Consulting Fees 8 Net Profit 9 10 Central Division 11 Sales 12 Cost of Sales 13 Payroll 14 Lights 15 Net Profit 16 17 West Division 18 Sales 19 Cost of Sales 20 Payroll 21 Lights 22 Consulting Fees 23 Net Profit Excel tables to the web >> Excel Jeanie HTML 4 So this formula picks up the first Consulting Fees after the Central Division. The problem is it is going to pick it up under the West Division. So, how would I modify the formula so that if the row number is also less than Central Division Net Profit (A15), it is zero. (no offset or indirect)
View Replies!
View Related
Lookup Until Conditional Match Then Return Last Value In Row
I am trying to modify this formula to do one more thing, but I can't quite figure out how to do it. I have part 1 and 2, but part 3 is giving me a very hard time. The formula is not working correctly. It is supposed to do several things. Do an HLookup for a row and return the last value in the row.If there is no value (blank), the value should be "Incomplete."If HLookup finds a value of "Fail," it should look to see if there is a value of "Pass" BEFORE (to the left of) "Fail." If this exists, then it should return a value of "OK." Here is what I have so far....
View Replies!
View Related
Lookup Maximum Value & Return Corresponding Row
i have a matrix index| a| b| c| 0001 |1| 2| 2.22| ( probably it will be much clear to see the attachment) I need to lookup for the largest number in B2:D2 (in this case 2.22) and return the corresponding header ("C"). I though it would be simple with hlookup() and max() but i can't make it work.
View Replies!
View Related
Lookup Multiple Columns And Return Top Row
I've been working this for ever and can't seem to figure out the best way to go about it. I have attached an example sheet. All I need to do is figure out the Dept #... which is listed in Row 1, Column F:H. I want to match the project numbers and then return the AA, BB, or CC in Column B.
View Replies!
View Related
Lookup Value In Row & Return Result X Rows Down
I am working on a spreadsheet for some packages we are sending to multiple recipients. I have figured out how to get all the weights calculated, now my Mailing Manager has asked me to come up with a calculation for the actual postage amount for all the 1400+ packages...Unfortunately it is not as easy as that would seem. There are 9 different mailing zones and each zone has 19 weight categories and each category has a corresponding postage amount for the package. So I need to come up with a formula to calculate the package postage amount based on those multiple categories. For example: Zone 1: 0-1lb = $2.12 1.1-1.5lb = $2.12 1.6-2lb = $2.21 etc How do I go about entering in that information from which a formula can determine the proper package postage cost and what formula do I need to use in conjunction. This is all done within a address list so each package is a row and all weights and zones will be in a separate column. Package is in zone X and weighs Y pounds therefore the postage is Z. Something like that.
View Replies!
View Related
Lookup Maximum & Minimum. Return Corresponding Row
I have searched your forums and thought I had found a sufficient answer but could not get the vba to work. So any help is greatly appreciated. I am trying to determine a max value from a list then put that value in a cell. Next I want to determine how many times and on what day that max value occured. From there take the value and concatenate them adding a "," between them I have attached an example. I would like the values placed in cells F1 and H1 (the other is a min value and when it occurred)
View Replies!
View Related
Lookup Date Heading & Offset To Left
In the attached spreadsheet you will see the table of data in cells e6:L16. In cell N6 i am doing a hlookup to find the date in the table of data that is closest to the date in cell A4. The formula in cell M6 is where i am having the problem and i was trying to pull in data that is 4 columns to the left of where the hlookup date is in cell N6. The problem i am having is when i input any date higher than 06/30/07 the Index formula does not display the correct date. What is confusing me is that the Hlookup formula in cell N6 still works with higher dates but the index formula does not.
View Replies!
View Related
Lookup & Return Data From Different Column
I have a book with multiple tabs in it. The first is a master customer file, including customer numbers. The next two tabs include multiple references to the same customers because they list multiple shipping addresses and contacts for each customer on a separate line. I want to be able to have the additional tabs refer to the master list and find the customer name, then return the corresponding customer # in the previous column for that customer. This is what I came up with: =IF(B:B=Sheet2!B:B,Sheet1!A:A,0). But the results simply returns numbers that increase by 1. It should repeat the same customer # for the number of times that customer name is listed based on how many addresses or contacts there are. What am I not doing right?
View Replies!
View Related
Lookup Data Without Knowing Return Column
Got a bit of a stinker here. (Well, it is for me anyway. I haven't used Excel in months)! I want to use a VLOOKUP function (I think) to retrieve data from a list of Exchange Rates on a separate sheet. The user enters a 'Period' number, selects an 'exchange' variable (the blue text cells). The hope is to lookup the data on the 'FX RATES 06-07' sheet and return the result in the green cells. I've tried using VLOOKUP but one of the arguements specifies that you nominate a column to return the information - that's the problem, we don't know that information when asking Excel.
View Replies!
View Related
Return Heading At Point Total Is Met
I have a spreasheet with partnumbers listed in column A, a safety stock value in colum B, a restock date in column c, and quantities listed by month needed in the following columns, i.e.: A B C D E F G Part # Safety Stock Restock Jan Feb Mar Apr 123abc 50 Feb 20 35 20 10 In the example above, it is apparent that the safety stock will be delpeted in Feb. I am trying figure out how to calculate the month that the safety stock will be depleted and show this in another cell.
View Replies!
View Related
Lookup 2 Criteria And Return Info In Specific Column
I've got a database that is sorted by date... I need a formula that will look for 2 criteria and once it finds those 2 matching criteria, I need it to return the information on that line that's in column 6, let's say. so in one column I have the date, the next column I have the sales persons name, 4 columns over I have their order number. In cell a1 of worksheet 1, I have a drop down ready that has all my sales persons listed. In cell a2 I will manually enter the date that I need to reference. I need the formula to then look for cell a1 and a2 in worksheet 2 where I have my spreadsheet with the info I mentioned above, match that criterium in worksheet 2, and return the info in column 6.
View Replies!
View Related
Index Match Lookup, Variable Return Column
I would imagine that the title of this thread made little sense, so let me try to explain: Sheet A has columns A-C populated with data. Column D needs to search Sheet B columns A-C for a match. (ie, see if the data on sheet A is also on sheet B) if this is true, then it needs to return the value of the LAST column of the matched row in Sheet B. So, if Sheet A R1 A-C is 1,2,3. Sheet A R1 D needs to search Sheet B for 1,2,3. and when it finds it, (for example on row 9) return the value of the last column of Sheet B row 9. The issue is, the last value could be in column R or S or AA, there is just no way of knowing.
View Replies!
View Related
Lookup Partial String To Return Data From Corresponding Column
I have a simple lists containing two columns. One column contains a five digit number and the other a vendor name. The vendor name in most cases is two to four words. I am wanting to type in a partial string of the vendor name and it return to me the 5 digit 'vendor' number. Col A Col B 20567 3M Electrical, Inc. I want to type in '3m' or '3M' or 'electrical' or 'ELECTRI' and it return the 20567. The other part of this is that there may be two rows with the same info in which I would need to see both... Col A Col B 20567 3M Electrical, Inc. 21789 3M Tape Division Is this possible with standard lookup features in Excel or does someone have a VbScript or macro that will accomplish this?
View Replies!
View Related
Return Cell Reference From Multi-Column Lookup
i just want to use vlookup and hlookup to give me the row and column headings for 30 to 40 sesor at a time.... but i keep getting #n/a whenever the functions meet a table of more than one column! (reduced workbook attached) it should surely be simple to get this data - but i've struggled to no avail. I thought that having the four separate worksheets was the problem - but i haven't had any look even when i dump the data into a single worksheet! i basically just want excel to return the cell reference of a sensor number which exists in a table. sometimes a sensor can appear more than once, but its not very common and i could happily work around that by doing the manual search (ctrl f, find all). any advice would be very much appreciated, i'm struggling and the number of sensors i need to test will increase in the coming months.. please help! should i even be using vlookup and hlookup? ive tried all the other excel functions, but they don't seem to be useful?
View Replies!
View Related
Return Unique ID Based On Multi-Column Lookup
I'm looking for a formula (VBA I'm assuming) that will help me create a unique customer ID out of data that my website generates in order to import records into my accounting system. I have a current list of customers in CSV format with the columns: CustomerID, CustomerName, CustomerZip Each customer in our accounting system is assigned a unique,7 digit CustomerID in the format of XXX#### where XXX are the first 3 letters of their last name, and #### is a 4 digit number (with leading 0's) to create unique ID's for customer with the same first 3 characters of their last name. SAMPLE LIST: SCH0001, Lindsey Schubert, 75230 SCH0002, Thomas Schoembs, 53132 ADA0001, Samantha Adams, 28205 ... What I'd like to do is pass the formula 3 parameters (Cust_First_Name, Cust_Last_Name, Zip) and have it parse the .CSV file and either return an existing customer's current ID or generate the appropriate new, unique ID, making sure in increase the 4 digit # accordingly and insert leading 0's if necessary. Another caveat, if possible to work with, is the ability to also pass the formula another range of cells to append to the end of the .CSV file's data for comparison reasons. There are times when I'll bulk-import orders (or we receive numerous in the same batch) and the potential exists to have two customers that would have the same CustomerID created using JUST the .CSV data. Ie. If we use the example above and have new customers of Steve Schwab and Julie Schwitzer - we'd end up incorrectly assigning them both SCH0003, where if we'd read Steve Schwab's newly created info and customer ID of SCH0003, then Schwitzer would correctly be assigned SCH0004.
View Replies!
View Related
Lookup Left Most Column & Return Adjacent Value
Basically what I am trying to do is use a Lookup formula on a sales invoice where the product name and product price will be automatically entered into the sales form when the product ID is entered. What would the formula be? Product Information RegionProduct IDProduct NameProduct Price Product IDHPPHomeware Pizza Pan19.95 Product NameSPPStone Pizza Pan 29.95 Product PriceHCDHomeware Casserole Dish19.95 Units SoldHCPHomeware Cookie Pan 9.95 Total SaleCWChina Wok 19.95 HEWHomeware Electric Wok29.95 WCMWilson Coffee Machine29.95 HBMHomeware Bread Machine49.95 HBMDHomeware Bread Machine -Deluxe89.95 HRHomeware Rotisserie119.95 OGGOlson Gas Grill 159.95 OEGOlson Electric Grill 159.95
View Replies!
View Related
Return Heading Ascociated With Date Found In Table
Attached is a very small sample of a workbook that looks at community impact; before, during and after a project. The source data (not shown) is down loaded from a system that can only supply the date in reverse order (see column A). i.e. 20080229 = 29-Feb-2008. I've applied a conversion in column "C".... = DATE(LEFT($A2,4),MID($A2,5,2),RIGHT($A2,2)) The 'properly' formatted date is then used to determine when on the scale of observation that given date falls. e.g. the number of weeks before, during and after the project. This is determined by the 18 week date 'matrix/ array' in columns F to X the red box is used to input the project start date, (& assumes the project runs to its normal 6 week time scale, it get REALLY messy if the boss prolongs or cuts short the project!) From the result various figures and a graph using the properly formatted date are calculated, produced and presented showing.... The scale of the problem {before} The impact of the project {during} The lasting efftectivness {after} You can see that I've managed a 'workaround' for the limit on the number of IF statements allowed in one formula using a rolling formula in columns; B then Z - AQ. This works OK, but!! combine that with the other formulae doing the impact calculations and 20000+ rows of data (with the acssociated workings in B + Z-AQ) the effect is to make make the file over 15Mb for 1!! worksheet, (the limit for emailing on the jobs system is 3Mb!!) & just to really make it scary sometime there are 2 projects on the go at once. Before I created the attached the user responsible for these graphs and calculations manually counted dates & typed/copied & pasted the week number/tile, initially several days work followed by half a day every weekly update!
View Replies!
View Related
Search Table & Return Heading Of Found Value
In a large spreadsheet that receives external data, I have codes (U, N or V) allocated at various times to different rows as shown in sample worksheet attached. Elsewhere in the spreadsheet, I need to display (for later export) these codes along with their respective number in a sorted list. Please review the attached: In the spreadsheet you'll see a column of codes, the next column is the data reference number, then to the right is three columns, one for each code. As displayed in the sample book attached, each Code column is to display the data reference number (from column 2)that matches that column's code. What formula can I use to list these numbers in the appropriate columns?
View Replies!
View Related
Match Text In Column Of Another Workbook & Return Nth Column Same Row
I have two excel files. File #1 is a varying length and has 4 columns with ID #s in Column A. File #2 is a two column file that contains a list of ID #s in column A AND modified ID#s in column B. I need to match the ID #s in column A from file# 1 to the list in column A of file#2 then replace ALL instances of the matching ID #s in column A of file#1, with the values found in column B of file #2. I've tried using the substitute function but I could only replace one found ID at a time.
View Replies!
View Related
Lookup Column Values In Another & Return Value If Match Found
I need to create a macro that will loop through a list of names on Sheet 1 / Column A, and if that name is listed on Sheet 2 / Column A, then it will set Sheet 1 Column B (for that one name) to "1". The problem I've run in to is that both the lists change in length each time I have to run this (so no set range). I've been able to get this to work with two ( nested) loops, where the first loop goes through the Sheet 1 list and the nested Loop looks for the name on Sheet2, but it is extremely slow. Is there any simple VBA code or formula that I'm missing that could do this? I've tried to do something with the " Find" VBA command, but I can't see how it would apply to this if the list changes in size.
View Replies!
View Related
Lookup & Return Values Of Adjacent Column To Textbox
I have an excel sheet with cols of information e.g IDNo, FirstName,LastName etc. I have created a form in VB where you enter the IDNo (& Click on a command button called View) It should populate designated textboxes on the Form. Thus it should check the col where the IDNo is stored, find the IDNo and populate the adjacent cols i.e FirstName, LastName in designated textboxes on the form.
View Replies!
View Related
Find Value In Column & Return Cell On Same Row- Different Column
I have an excel sheet I am working on and in columns F1:F2000 I have an IF statment, I need to be able to add more "IF"s to it but I will exceed the 1024 char limit. Is there a way I can put this formula into VB as a function called DocumentType() and then in excel F1:F2000 =DocumentType()? Is it as simple as: Function DocumentType() If(...........) End Function
View Replies!
View Related
|
TRACKER
