Lookup Match And Return Comment Based On What Is In Relevant Column
Jan 15, 2014
The logic of the formula like this....
TAB X, Column A contains a data set, each row is a unique value. In column B of TAB X, I want to input a formula that performs the following logic: Search TAB Z, Column C, for an exact match of data contained in TAB X, Column A....and if found, look in TAB Z, Column D...if a blank cell is contained there, then in TAB X, Column A, say "Unsettled" ; however, if there does exist any character (not-blank) in TAB Z Column D, then in TAB X, COlumn A, say "Settled".....but, if the data ID listed in TAB X, column A is not found whatsoever in TAB Z, Column C, then say "NOT FOUND". So for example, within TAB X, Column A there is a unique ID in one of the rows "ABC123", then searches "ABC123" in TAB Z, Column C. If found, it looks in TAB Z, Column D and that cell is blank, so TAB X, Column B, same row that contains ABC123, the result is "Unsettled".
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D. e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
I have a tab that has 2 columns of data and I want to be able to return a value in column A if my data matches column B. If column B has the text TRUETRUE, I want to bring back the corresponding data in Column A. How do I return all the data in Column A for all the TRUETRUEs in column B? I can only get the first instance of TRUETRUE.
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.
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.
I have two excel sheets. The first sheet has a column with codes and another column with dates. There are more than one date for the same code. The second sheet has just one unique code. I would like to match the code on the second sheet with the code on the first sheet and return ALL the dates associated with that code to the second sheet - horizontally.
I have a column (B) of randomly generated numbers 1-14, and am using this formula range to sort in descending order and return the relevant value from column (c).
=VLOOKUP(LARGE(B3:B20,1),B3:C20,2,) to =VLOOKUP(LARGE(B3:B20,14),B3:C20,2,)
Works great, except when a number is duplicated, (E.G. 14,13,13,12,11,10,10,9,8 etc). It then returns the first value from(C) repeated, and not the value from the second and subsequent duplicated reference numbers.
I'm working on a project in which I have 50 states + DC listed by 2-letter abbreviation, and want to return a value in another column that will be a non-unique number (some will duplicate in the second column.)
Also need to do this same operation with first column (state) and third column, which also is comprised of non-unique numbers.
Hlookup, lookup & pivot tables seem to not be a fit for this.
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:
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.
I have a spreadsheet that contains data for a fiscal year broken out by month and quarter. I want the formula to return the data from that month and use whatever the latest quarter is. For example in the data below:
If it has only pulled data only thru Q1 it would return Q1 for Jan-Mar, however once I have Q2 data I would want the formula to return Q2 for the months of Jan-Mar.
2013Thru Q1STOLISTOLIJan-2013STOLI BASE 2013Thru Q1STOLISTOLIFeb-2013STOLI BASE 2013Thru Q1STOLISTOLIMar-2013STOLI BASE 2013Thru Q2STOLISTOLIApr-2013STOLI BASE 2013Thru Q2STOLISTOLIMay-2013STOLI BASE 2013Thru Q2STOLISTOLIJun-2013STOLI BASE 2013Thru Q3STOLISTOLIJul-2013STOLI BASE 2013Thru Q3STOLISTOLIAug-2013STOLI BASE 2013Thru Q3STOLISTOLISep-2013STOLI BASE 2013Thru Q4STOLISTOLIOct-2013STOLI BASE 2013Thru Q4STOLISTOLINov-2013STOLI BASE 2013Thru Q4STOLISTOLIDec-2013STOLI BASE 2013Thru Q4STOLISTOLIDec-2013STOLI BASE 2014Thru Q1STOLISTOLIJan-2014STOLI BASE
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
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)
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."
If lookup doesn't find a match in the first column, can I get it to return an "ERR" or "0", something that would let me know there was no match? Right now it returns the value in the second column of the closest value.
I am trying to create a quotation spreadsheet and want the drop down list to contain the materials but once a selection is made in the drop down list, i want other cells to be accordingly populated with data stored on another worksheet within the workbook.
The source of the drop down list is material and i need other cells to be populated with the adjacent data.
Here is a small list i have at present, which will be much larger when completed.
MATERIAL 20mm 30mm Absolute Black £62.15£83.30 Absolute Black Premium£71.65£96.10 African Red £58.20£78.00
So if the selection in the drop down list is African Red then it should accordingly populate other cells with the values £58.20 and £78.00. Please help as i have tried many sources elsewhere but without any luck and this would be very benficial.
I am trying to do what I thought was a simple look-up. On one sheet in colum A I have some text and in colum B I have a number. On a second sheet i have a list of text that may or may not match the text on the first sheet in colum A. If the text in colum A matches one of the text items in the list, I want to have the value in colum B appear next to it in colum C. I tried to modify a vlookup formula but would only get a value error if there was a match.
I'm trying to make a budget type spreadsheet for a club I run. I have a popup box where I click what was purchased, and now I want a drop down list of all the members of the club so that when I choose one name it is inputed beside what was purchased (I hope this makes sense !). I've managed to make a dropdown list in a dialog box but I don't know how to make it so that the name I choose is inputted into the spreadsheet.
Working on an export from an e-commerce application that lists the item purchased, unfortunately not by item #, but rather a lengthy description. I'm trying to construct an item number based on that description which contains several constants.
Each item ordered appears in a single row, comprised of ORDERID, QTY, PRODUCT DESCRIPTION
I have several tables to pick out PRODUCT TYPE, COLOR, SIZE, etc. I need a formula to search to see which of the unique values appears in each in the cell and return the shortcut of that value.
PRODUCT DESCRIPTION example "Sweater - Blue - Large - Wool"
TYPE TABLE: Sweater SWTR T-Shirt TSHT Jeans JEANS
COLOR TABLE: Blue BL Green GN Purple PL Black BK
I would have separate tables for each part of the description.
The formula I'm seeking would search the PRODUCT DESCRIPTION cell for the range of values for each 'part'. So it would search to see which of Sweater, T-Shirt, or Jeans was in the cell and then return the one that was - SWTR in this example. I would modify this formula for each 'part', in its own cell, and then use an =cell#&"-"&cell#&etc... to combine these returned values.
I know I can write a large if- chain using ISERROR & FIND, though some of these lists will get lengthy.
IF/ AND/OR I have 6 tables on one sheet that I want to match data and print out the name of the relevant table ie brown, white ,pink etc
There needs to be a data match in both columns of a table before it prints the name of that table. I have tried the following but haven’t quite got it right:
What im trying to do is match columns A & B from AUDIT Sheet to Columns A & B in MASTER sheet. If they match then pull columns C, D & E from MASTER into AUDIT.
I'm struggling with match, index and lookup functions. I have a formula in column D that calculates the percentage of answers against the total score for that category. The way it calculates enables me to delete rows if they are not needed and still provide a total count and a percentage score.
Now the percentage is returned from a dynamic data set, I want to plot the percentages on a spider/radar chart. Instead of manually creating the chart or using a pivot, I wanted to lookup the percentages (column D) against the category (Column A) in question and plot so the chart updates as the user changes the answers.
I have a if function that i am creating in excel 2000, and it has 8 variables. I am trying to put it in a macro as it is not working as a nested if function. Here is the current if function...
I have a spreadsheet with multiple columns. (Sheet1) The heading row is my customer name. (Sheet1) My column are the dates. On another spreadsheet, I would like to find the customer in Sheet2 in Sheet1, then do an index to find the relevant data I need, returned in Sheet2.
Sheet2: find customer "ABC" in Sheet1, if found, then find the date that also matches Sheet1 and Sheet2, and return data "apple" .
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.
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A B C
1 Cat 1 January 1, 2014 John
[Code] ..........
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example; C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1 C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1 C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
I have 2 worksheets with thousands of rows. I need to lookup 2 columns (customer account # and number of occurrence) and then return a 3rd column (type of occurrence).
How do I do this? Normally I use sumproduct but in this case I need the result to be the text value for the type of call, not a count.
I have a database of customers that are all sorted by a customer number.
I have a form that has a number of fields that display customer info. When I use a blank of this form and put the customer number in it's correct field, all the other info fields do a vlookup on the customer number in the database file and return the info, such as phone numbers, name, address, etc.
Here's my question;
How can I check for a blank (using ISBLANK, I assume) to check to see if the customer number exists in our database and, if it's NOT blank, operate on it with the following example which is used to return their insurance expiration date.
I realize that this may be a sledgehammer approach and that Access is probably a better tool, but my company has not shelled out the $$ for Access...yet.
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.