I am looking for a formula, to change from this
IF Formula located in Cell F15
=IF(E15=A15,B24,"")
to give the If a Range.
I thought it would simply be:
=IF(E15=A15:A39,B15:B39,"")
But all I get is Blank Cell.
B24 has a Value but it does not appear in F15,F16 and F17 respectively.
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.
View 4 Replies View RelatedI 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.
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 9 Replies View RelatedI want to lookup the max value within a group of columns and return the column heading (specified a cell) that corresponds to the max value.
View 9 Replies View RelatedEncryption 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 recently learned how to count cells in a range based on the value from another column (excel 2007) How to count cells in a range based on the value from another column but now how I can go about returning a value from another column that matches the conditional counting. For example in the table below I'm first wanting to find the rows matching "chr15" from column A that also have a value from column B that is greater than 25,000 and less than 3,000,000. But what it I wanted to instead report the corresponding values in column C? I've played with VLOOKUP to no avail but I'm not sure if that's the right line of thinking. The answer would be the values in bold.
A B C
1 chr2 12008 AA
2 chr2 149700 BB
3 chr15 51 CC
4 chr15 5624 DD
[Code] .........
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.
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.
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
I have a sheet with Customer name in the first column and next 7 columns have unique serial numbers(alphanumeric) of the systems which that customer has purchased. In another sheet, i have the unique serial number in first column and I want to search this number in the 7 columns of other sheet and return the "Customer name" to this sheet. How do I do that ?
Vlookup searches only in 1 columns, so does match index. lookup didn't work as well.
I would like to write a formula to return a value from a column that relates to an array within which my lookup value exists. See the tables below:
ID
V1
V2
V3
105
27
3
149
[Code] .......
The tables above are on different sheets within a workbook. I would like to write a formula that returns the "ID" numbers from column "A" in the first table, based on values in columns B-D in that first table, into column "C" in the second table. For example, in this case, the ID number that corresponds with the value "12491" would be "109", since 12491 corresponds with 109 in the first table.
FYI, the "Value" numbers in the second table are calculated based on their rank (high-low) within the matrix in the first table.
I tried the INDEX-MATCH function, but it doesn't seem to work if I'm trying to find a value within a 2-D array- it only works if I'm looking in a single column.
I have the below data. I'm trying to create a lookup that would return the Month and Year in another cell. For example, I have in another cell the min of all the data, I want to get the month/year in the cell below it with lookups. I've tried INDEX/MATCH, but not getting the results I want.
Excel 2007ABCDEFGHIJKLMN3JanFebMarAprMayJunJulAugSepOctNovDecYTD420134.47 (0.35)0.86 4.98 520121.27 (2.29)(0.27)1.55 4.61 (2.33)2.07 1.38 0.62 (0.60)2.50 0.12 8.63 620111.41 3.69 3.97 2.49 2.06 3.14 (2.97)5.83 (1.26)(1.30)0.34 (0.36)17.04 720103.34 2.24 1.59 (0.33)7.70 0.63 (0.26)2.00 2.78 2.81 3.01 7.96 33.47 820098.51 3.12 (1.95)(7.30)7.91 8.94 2.90 0.41 0.66 0.67 1.39 3.10 28.36 920084.09 1.60 6.83 8.65 4.42 6.79 11.01 8.16 1.03 18.41 17.74 7.01 95.74 1020072.08 (0.62)6.02 (0.30)4.74 (2.28)4.04 (1.12)1.29 0.89 11.58 1.79 28.11 1120064.87 (1.83)4.56 5.04 2.19 0.53 3.55 6.00 (0.68)2.76 2.82 4.74 34.55 1220052.43 3.17 1.71 4.44 5.78 5.84 (2.61)5.21 0.04 6.34 1.87 5.76 39.98 132004(0.22)4.98 2.86 (0.52)4.80 3.22 3.45 1.97 2.77 2.61 25.92 Portfolio Summary
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.
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'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.
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?
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 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 3 Replies View RelatedI 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.
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'm using a formula to lookup names within text and return that name to a separate column if it's found.
The formula is =INDEX($E$1024:$E$1026,MATCH(FALSE,ISERROR(FIND($E$1024:$E$1026,E1010)),0))
I understand index and match functions, but I'm confused regarding the use of FALSE as the lookup value and the iserror(find( usage.
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.
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.
View 5 Replies View RelatedI have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.
View 4 Replies View RelatedI have this vlookup formula and as you can see when I run it works good. The only problem is that I would like to have formula that it something is not found I don't get #N/A , but rather a 0 or blank.
=VLOOKUP(A6,GL!A$1:E$76,5,FALSE)
LOOKUPDIVDPTCC SALES 46120
010775010775 49,028,244.58
010200010200 3,373,145.76
010700010700 113,667.00
010300010300 14,382.54
011000011000 (1,518,076.05)
010600010600 589.98
011900011900 6,280.31
010900010900#N/A
011100011100 313.08
010750010750 570.90
Column A lists types of employees (managers, engineers, sales reps, etc.), each of which earns a seperate hourly rate. Column C lists hours worked, and Column D lists the total pay (Cx*Bx).
So column B is missing. Is there a way to generate the hourly rates in column B for each employee type in collumn A, i.e. if it's an Engineer, return $29, if it's a Technician, return $18, without an infinite set of True/Fasle conditions.
I have two files with the below data:
File A
Bob Jones
Williams, K.
File B
Jones, Bob
Williams, Ken
The only commonality between the two files is the last name. I can easily separate the last name off File B, since it is always 'last name, first name'. I want a lookup formula to use the last name from File B (after I separate it), lookup to File A, and if it finds a cell in a specified column that contains the same last name, return data from another column within File A.