How to return all possible values based on a single lookup ( or another condition / macro )
I have a table in B5:E100
In A1 I have a value
I need to look in B5:B100 for the value in A1 then place the contents of B:E for those cells in G5:J5 downwards
I have an array in excel, 4 columns: Month, Day, Stock, Price. I dont know the most efficient way to handle arrays. I know i can loop through an entire array to find a specific value, but if i have a large array, this doesnt seem efficient.
For example, I would like to find the price in an Array (or Range), when i know the value of two columns. If this were in a database, I would write sql like this: " select Price from TABLE where month = 1 and Day = 3". The result would be 40.21. How can i do this with an array? I have attached a sample spreadsheet with the example mentioned above..
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.
=IF((VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE))<TODAY(),"EXPIRED",VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE))
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 used the below formula which works in a standard cell, however I would like to have this is a VBA code using the Target.Offset option, however I can not get this to work. There may also be a better way of doing this.
The code is looking to see if the 1st cell (A118) is empty or not, if it is empty do nothing otherwise it then looks up the value in the 2nd cell (B118) and assigs the appropriate name from the range values.
VB:
=If(A118="","",LOOKUP(B118,{0,0;0,"";1,"Main Bank";71,"PFS";80,"Main Bank";106,"Dry Clean / Photo";112,"SCO";141,"Cafe";168,""}))
I have attached an example. I am wondering how I can get [K2] to return the value 501 based on the lookup.
View 7 Replies View RelatedI have four columns, A through D
Column C is returning a simple vlookup of A
I need Column D to return a value where C is TDMA return TDMA or when C is GSM lookup column B compare to tab2 (columns A through L) returning column 12.
I am currently looking at the workings of a spreadsheet designed by someone else.
First of all i need to know,how these combo boxes are created in the attached sheet,because it cant be edited.in addition to tht in the coloured cell (F17)i'm trying to dereive a formula which is,if (C17 = doll "1"),but its not working.Please someone give me a solution.
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.
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 have two spreadsheets.
spreadsheet 1:
Lookup from Order numbers listed from A5:A177.
requested formula in I5: I would like a lookup to sheet 2 based on the order number (F19:F191), to return the cell above the first non-blank value.
spreadsheet 2:
Lookup value:Order number listed from F19:F191.
Data search:AY19:CI191
return the (date) which is in the range above the data search from row AY18:CI18.
I've had a look at few forums but i'm getting mixed responses, having to use index / match / lookup / min / --.
I want to lookup the tags in sheet1 on sheet2 and see if they have a date in columnB. whatever date is in columnB sheet2 i want that same date to show up on sheet1. but if the there is no date i want the cell to remain blank. do i make any sense at all? look at my example. example2.xls
View 3 Replies View RelatedI 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 9 Replies View RelatedI have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies View RelatedI would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.
Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
Encryption algorythim.xlsx
Here is my scenario I have all this data i want to search in. In the rows there are repeating values and in the columns they do not repeat.
As seen in the attached i am able to give a column and row by title and then get the intersecting cell data from that. (Encryption lookup)
What i want to do is give the column tittle and row value and get back the row title. (Decryption Lookup)
This shouldn't be too hard seeing that the data in the columns has no repeats.
I have all my data on sheet 2 which contains tables that relate to information on regions, manufacturers, etc so it can be a large amount of tables which will change from month to month depending on a monthly list.
For example, if “Region 1” is in cell B134 – the resulting data I need to pull out will be contained in C138:G232 ...or ”Region 2” which is in B235 – table info is in C239:G333, etc, etc.
On sheet 1, I have a cell (L7) that is populated by another formula. This cell is effectively my lookup to pull out information from sheet2 to populate cells in (sheet1) AE119:AH212 - same sheet as the reference (L7).
I need to look up the reference/resulting value that is in L7 on sheet1, find and match against the values in column B on sheet 2 and then pull in the corresponding table information.
If the value in L7 was not changing at all I could do.. in cell AE119… =OFFSET(sheet2!B134,4,1,1,1) etc etc
... but I am at a loss as to the value in L7 changing and incorporating a lookup… or maybe I am looking at it wrong..?
I have looked up match, offset, index and lookups on the forum and have managed to confuse myself even more. I have even tried taking some of the example formulas and amending with my references but to no avail.
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.
I have two excel workbooks, i.e target.xls and source.xls. Assume
target.xls looks like this
letter number sound
a 1 a
b 2 be
c 3 cee
d 4 dee
e 5 ee
f 6 eff
g 7 gee
source.xls looks like this
letter number
a I
c III
d IV
g VII
In the target file, I want to be able to change the numbers of the letters found in the source file, so that the new target file will look like this.
new target.xls
letter number sound
a I a
b 2 be
c III cee
d IV dee
e 5 ee
f 6 eff
g VII gee
I believe there must be a simple way to do this using VBA.
I'm trying to compare 3 different worksheet in the same workbook and copying the unmatched values into a new worksheet. find the attached file. In the sample file, am comparing sheet1, sheet2 and sheet 3 with sheet1 as the base. the columns mite differ in their location but the column headers are same.. The difference, bolded cell, should be copied along with the header and the code, into a new file and has to be saved...
View 2 Replies View RelatedThe main worksheet in my workbook contains lists of suppliers with unique Vendor Numbers, of which there are approximately 4,500 rows. There is another worksheet which holds approximately 6,500 entries I need to bring data from that sheet to the main sheet using the Vendor Number and the information that is held on 4 columns unique to that number.
The information is a classification system that comprises of 4 columns holding a mixture of numbers and text.
Worksheet 1 has the Vendor Number in Column A and the Classification destination starting in Columns Q, R, S & T.
Worksheet 2 once again has the Vender Number in Column A, and the Classification information in Columns C, D, E & F.
What I need guidance on is how to build the formula to copy the information across, or direction into what other method I should be looking at instead.
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?
I have a worksheet containing a list of states in column A and a list of companies in column B (along additional data in columns C-L). I want create a lookup formula on a separate worksheet where users can select the state from a dropdown menu and it will return the information from columns B-L for that state.
For example:
A B C D
Alabama ABC Company Active Expires December 2009
Alabama 123 Company Expired Expired April 2008
Alabama XYZ Company Active Expires August 2009
Alabama Larry Company Expired Expired May 2006
How can I do this without losing my mind? I've tried various Index, Small and Match formulas and none of them work.
Use look up to check batches of data a return a value dependent of multiple ifs?
I have this formula from here
=LOOKUP(COUNTIF(C4:C7,""&0),{0,1,2,3},{19.9,29.9,39.9,49.9})
What I am trying to achieve is using the above can it check
C4:C7 Grouped and named "first"
C8:C10 Grouped and named "second"
C11:C13 Grouped and named "third"
If one from each group is selected it returns 1 value, if 2 of each are selected it returns a second value and if 3 of each are selected it returns a third value?
I thought by grouping them it may give me the desired results but all it does is change if I select more than one from any of the groups.
I need to pull info to additional tabs. The problem I am having is that the data is sorted daily based upon performance numbers, so the REP names in column A can change based upon the data reported in column C.
What I want to accomplish:
Pull daily data for each unique name in column A to a team tab/worksheet that will be used to report that team's daily data.
Below is a representation of the data I need to break down by Rep Name and Team.
Rep DATA DATA DATA DATA DATA DATA DATA
1 2 3 4 5 6 7 REP 2 27.5% 14
51 86 REP 1 33.3% 14
42 74 REP 3 19.3% 11
57 86 REP 5 19.6% 9
46 66 REP 4 33.3% 9 ...........................
I have a excel spreadsheet that has employee names, email address and DOB. The DOB format is 23/03. I need a program that will lookup the DOB row and when it matches the value I enter it extracts the employee name and email address. Once it has this information I need it to copy into an already created email template and send its off. On some days there are more than 1 person with a birthday so it will need to send multiple emails.
View 7 Replies View RelatedAssuming I have a table such as:
APPLE BEANS CELERY DOUGH
Will 5 10 15 2
Joe 1 3 7 11
Ben 9 6 4 8
What formula can I use to return a result like Celery by looking up Ben and 4 for example.
In other words, how do I lookup the heading of the column based on the the first column and a data point on the same row.
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00
columns E, F respectively have cust #B100 & 40.00
columns G,H respectively have cust #C100 & 60.00
Etc.
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
show/display data based on month criteria, with selected month in cell H2 (as start date) and I2 (as end date) my expected result start from cell H7, i called "blue area"..
see my attached workbook..
I have a bit of a challenge. You'll have to take a look at the attached files to understand what I am trying to do. I am asking for your advice. I am creating multiple spreadsheets (QPPcompany.xls) that will refer back to a master data(QPPdata.xls) file.
I am trying to figure out how to best set up the data file so that I can do lookups on the data from the referencing files. Here's the challenge. I need to be able to find in the data page the company name in Column A and then how many L's (or C's or P's) they had in a specified month. So for instance I need to be able to find the number 3 in cell E6 and place it in B15 of the referencing file. The required criteria for lookup are Company Name, Month and then L, C or P.
So my question is, is this possible with how my data file is currently set up? I think it may be a problem to have the month/year in row 4 refer to 3 different colums for each month (L, C and P). I don't mind making changes to the data file to make these lookups possible and I think it may be necessary.