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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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 Replies!
View Related
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
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 & 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
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 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
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
Lookup Column Values On Another Sheet & Return Adjacent Cells
Essentially, sheet b is a list of 900 people I need to mail to. Sheet a has 3000 rows of people, many of whom don't need the mailing. It has mailing addresses that we need in sheet b. We have two worksheets, sheet a and sheet b compare sheet b, column c (email addresses) to sheet a, column x, (email addresses). If they match, move sheet a, columns d, e, f, g, and h to sheet b.
View Replies!
View Related
Formula To Go Through Each Row And If The 2nd Column Is Yes Then Return The First Column
I have a collection of data like the following: Week1 | yes Week2 | no Week3 | no Week4 | yes Week5 | no ... Week28 | yes I need a formula to go through each row and if the 2nd column is yes then return the first column. I could then copy and paste this formula into a the next cell, add an argument saying the returned value cannot be the same as the previous ones, and so on. Essentially this is a series of nested IF statements. However, Excel prevents more than 7 statements from being nested, when I need all 28 to be searched. I cannot think of a way to get VLOOKUP, INDEX & MATCH, or defined names to make this work, because it needs to step through each row.
View Replies!
View Related
Lookup Value In Row And Column
I am trying to create a formula that will look up the data in Sheet 2, A3 and if found in Sheet 1 to then look up Sheet 2, C1 in Sheet 1 and then enter the data if found into C3. Is there a way to do this? I have attached a sample workbook.
View Replies!
View Related
Lookup Using Row And Column Names
I have two workbooks used for periodically updating product pricing to wholesale customers, one of which has all the product computation info and the other all the customers and products they buy. There is one main sheet in the product information book that has all the products going down the first column with a code for each item. The headings of many of its columns represent variations in packaging. The combination of product and package variations result in the customer's unique price. I am seeking to find the intersection of product and packaging using names rather than cell numbers. Right now I am able to use VLOOKUP with the product name and named range from lookup sheet but I seem forced to use the column number rather than column heading name. As there are a lot of columns it's easy to lose track or make mistakes. Column name would be much preferred. We are still using Excel 2000. Example of current reference: =VLOOKUP("productname",mainlist,24,0) I would like to use something other than 24 for the lookup column, like 'packagename' instead. I have reviewed the INDEX and MATCH functions but haven't figured out how to accomplish what I am after. Incidentally, 'mainlist' is a named range in a separate workbook but referencing as shown above has worked without a hitch.
View Replies!
View Related
Lookup Value Using Row Match From Another Column
i'm trying to figure out how i could get the sumproducts or anyway to get this forumla work basically i'm trying to have the cost column to vary depending on the variable set on column d & e on the top basically ... * if d7 is between d2 and e2 use cost 1 for the multiplication to generate H1. * if d7 is between d3 and e3 use cost 2 for the multiplication to generate H2.
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
Double Lookup (lookup Variable Row And A Variable Column)
I have a file that I would like to lookup variable row and a variable column. I have tired vlookup and hlookup but these do not work because you have to specify a given column or row versus having that column or row be variable. Is there a way to do this. Think of a set of times tables. I would like to input 8 and 9 and get 72 as an output. How could I go about doing this? Or in the attached file, I would like to say A and 15 and get A15.
View Replies!
View Related
An Array And Return The Same Row In Another Column
I have an array with a lot of rows and columns filled with different codes. I need to look into this array for one specific number and return the value in the same row in an specific column. I can't use Vlookup in this case because it needs to have the values you are looking for in the first column and my values are spread in different columns.
View Replies!
View Related
Return Row# Of Max Value In A Column
I need (index? vlookup? if? match?). I did try this on my own and searched the board for help, but I couldn't seem to find something that applied. Sorry if this question is too simple or has been asked before. In Excel 2003 I want to return the row # of the maximum value in a columnar range.
View Replies!
View Related
Auto Return To Column A But Of Next Row
Is there an easy way to auto return to column A but of next row. I would like to be able to do this automatically say when column 87 is reached. I could just arrow down and hit home but there must be a way to automate this. I am currently in 2003 but will be moving to 2007 in a few months.
View Replies!
View Related
Paste A Value Within A Row Based On Column Lookup Containing Dates?
I'm looking to create a small section of VBA code on Excel 2007 that looks for a specific date on a column and then pastes a value on the correct row. I have weekly dates in columns along the top of my spreadsheet on row 3: C3 = 07/11/08 D3 = 14/11/08 E3 = 21/11/08 etc... The row further down the spreadsheet that I wish to use is selected by previous code and could be any row number within column B. I need a snippet of code that searches for a particular date along row 3, and then once it finds the correct column, it pastes the value in the corresponding cell on my active row. For example, if my active cell/row happens to be on B18, and the date I'm looking for happens to be on L3, I need to paste a value into cell L18. I imagine there needs to be some form of HLookup, but I've no idea how to implement this in VBA code.
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 & Return Value Same Row, Different Column
I would like to use a macro to find a number in a worksheet, other than the worksheet i'm using the macro in. The number - in this case a material code located in Column A - must be entered by the user in an inputbox, and then return (copy&paste?) the value of another cell (the price of the material - in Column D) in the same row. I recorded a macro envoking the find function, but then it only finds the number which I used during the recording. How do I change the code so that the user can input any number, or what code must I write?
View Replies!
View Related
Return Address,Value,Column/Row Of Selection
I am wanting to allow the user to select a cell in a worksheet and return the cell address and information/value from the cell. For example, if "Dog" is in cell A1 and "Cat" is cell A2 and the user initiates the Macro I would like the macro to to pop up a message box and say something like "Select a cell in Column A". When the user clicks on cell A1, I would like the message box to pop up and say, you selected "Dog" in cell A1.
View Replies!
View Related
How To Lookup Row # Based On Content Of Another Column's Cell
I have a 3 column table with 20 rows. The first two columns contain travel expense data. The cells in the third column are blank exept for whichever cell I enter an "x" in. Please let me what formula or worksheet function do I use to calculate the travel expenses based on the data in the first two columns that correspond to the row of the cell with an "x" in the third column?
View Replies!
View Related
Lookup Row And Column As Criteria But Data Is Spread Across Columns
I need to bring in values into one worksheet from another worksheet using row 1 and column A as criteria. I have previously done this using Sumproduct. However the complicated issue with this new worksheet is the setout. The source worksheet (see attached) has the actual data spread across columns. How can I bring in the values to the 'Summary' sheet from the '0607' sheet using the Employee number and the seg code as 2 criterias to lookup and bring in the resulting data (which is spread out in the purple area in the '0607' worksheet).
View Replies!
View Related
|
TRACKER
