i have a little problem regarding look/ find/search procedure.
i dont know what exactly the term of this.
anyway i have attached an .xls file for your perusal.
problem:
- i want to search a value in a certain table and the return would be the value of index cell.
I understand how to set up a normal VLOOKUP, but I am not sure how to do the following: I am trying to set up a sheet that will find the last cell of a row containing "yes" and display the value of the cell at the top of that particular "yes" column. Maybe VLOOKUP is not the answer? I have attached an example of what i am trying to do.
View 5 Replies View RelatedI have a range of cells (C29:BB29) containing zeros and integers, I want to lookup the smallest non-zero value from that range in another range (C11:BB11) and return the cell address from C11:BB11 to use in a further SUM formula.
Is that possible?
I have a XL work book with different worksheets what i need to do is compare cell a2 and e2 of worksheet 1 with all the data on sheet 2 and then return the data from the same row but in column m I dont know if this makes sense but it pretty much is the same as a VLookup but comparing 2 cells instead of one
View 9 Replies View RelatedI am looking for a way to construct a dynamic message box such that when a user right clicks on a cell, the macro will check the contents (a numeric code) of that cell against an existing table on another worksheet. This table would contain the codes and a text message specific to each code; the macro would insert the text message aligned with that code into a message box on the original worksheet.
View 3 Replies View RelatedI'm trying to return the cell address of an lookup. The lookup result is correct, but when I try use the below Address function, it just returns the text value (i.e., the formula itself) instead of the cell address. Below is the an example of each: ...
View 4 Replies View RelatedI am wanting to use a vlookup or perhaps an array(?) to bring through multiple cell values based on being the maximum value. So say there are multiple employees (all with unique ids) under one manager (also with unique ID), I want to find the employee with the highest value in a particular column and return that employees details, such as name, employee number and the value itself, which are all in the same row. I have attached a basic example of the data involved.
View 8 Replies View RelatedMy task is that I have several documents exported in excel. The requirements in them were mapped to another document. The IDs were populated in a certain column but are now no longer needed. I have to convert those old Ids to the current ones. In my example I have populated Sheet one with the lookup table. In column A are the old IDs. In Column B are the new ones they map to. The old ones can map to several which is why there are more than one ID in some of the cells. In column D of Sheet 2 I have populated it with the old Ids. In Column A I'd like to have the lookup return the appropriate maps to the new Ids. For example for the first map in cell A2 it should return:
abc_18
abc_43
abc_6
abc_4
all in one cell.
Vlookup can return the first hit (abc_18 and abc_43 for cell D2), but ignores the other two IDs in cell D2. I was able to work around this combining the find, mid, and vlookup to return all values. The problem with that is that the formula gets monstrously big after just having it return 4. In the actual files it can be up to 30 IDs which would make that formula literally pages long since. I have pretty much no experience with macros, but it seems like the only solution since I have to send this off to various people so they can use it.
The 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 data inputted into cells a-o in rows 1-250. I want to search, lets say row 1, (I do want to search all 250 rows if that info is relevant!) and if cell m is blank then I want to return all the info from row 1 into row 260. If there IS data in cell m then I want to return nothing. Bottom line I want to return all the data in all the rows that contains nothing in cell m.
View 3 Replies View RelatedAt work I have a spread sheet that I used to track material shortages by part number. So in column A of the spread sheet there is a list of part numbers that have shortages, column E contains a list of all sales orders that are affected by the shortage separated by a comma. I am trying to setup a query sheet where I input a sales order and get back a list of parts that are short for that sales order(basically reversing the original list to be by sales order instead of part number). The number of values in column E varies, sometimes a cell will have 1 value, sometimes 20+ and anywhere in between.
Example Sheet:
A
B
C
D
E
123
012
234
789, 567
465
789
890
012
I'm already got a INDEX/MATCH that would show both shortages for sales order 012. But I can not figure out how to get the shortages for 789 or 567.
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 have a table that has a number of new starters and corresponding appointments offered, what I originally required was to lookup the chronological date after the new start date.
However this has now been scuppered by my boss who has requested that not only lookup the date, but also add who the appointment is with, but if I do this the first array formula stops working and to tell the trust I'm not to sure how to do it anyway.
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 would like the code that allows me to copy enything I typing in column a sheet1 to column a in sheet2
View 8 Replies View RelatedWorking 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.
I have a list of dates in column AF. I would like to retrieve a value from the 2nd occurrence of a date, so I used the function for the Nth Occurrence on this website as follows:
Function Nth_Occurrence(range_look As Range, find_it As String, _
occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim rFound As Range
Set rFound = range_look.Cells(1, 1)
For lCount = 1 To occurrence
Set rFound = range_look. Find(find_it, rFound, xlValues, xlWhole)
Next lCount
Nth_Occurrence = rFound.Offset(offset_row, offset_col)
End Function
The excel formula I am using is as follows: =Nth_Occurrence(AF2:AF622,AE2,2,0,1)
Where AE2 is the date I am looking up. My question is this: When I put in a date ("1/12/2007") instead of AE2, i get the correct value. I tried giving AE2 and the AF column the same date formatting (3/14/2001), but this doesn't work. The date I am searching for also changes, so I cannot just type the date into the excel formula.
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 am performing a lookup using the populare user defined function nlookup, which does not take a lookuparray argument to find lookup values. But I only want to return lookup values that fall into a certain array. To accomplish this, I figured out that I need a way to specify a condition of the type "if cell address of lookup value falls within range"
It should be possible if I find a way to return the cell address of the nlookup value, but as nlookup is not limited by a lookuparray argument, so I was not able to use the address/index/match that come up in similar questions.
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 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.
I am trying to look up a value using VBA on 1 sheet and return it to a cell on another sheet.... I was hoping Evaluate sum product would work but I can't get it to. Basically I want to look up values in column C and D to locate the row then return value in column I specify.. below eg where Column C = SYD and column D = SHA, row = 6 .... value in L row 6 = 1
View 2 Replies View RelatedMy Lookup formula gives back N/A rather than an empty cell. I was unable to find the solution in frequently asked questions. Everything points to using VLookup which I feel doesn't fit my situation as I am only looking at one row of text.
The length of my targeted range changes so I am wanting to use the lookup for an extra amount of rows to ensure I capture all the data at any given time. Once my lookup gets to the end of the list and finds a blank cell i get back N/A. I tried to include "" in my formula to combat this but it isn't working. I have been using IF statements elsewhere on my sheet but there are 30 arguments and I was forced to find an alternative.
This is an abridge version of my formula:
=LOOKUP(Data!A2,{"","0129","0133","0163","0167","0187","0197","0202","0212"},{"","0201-0129","0205-0133","0201-0163","0213-0167","0201-0187","0216-0197","0212-0202","0217-0212"})
Didn't know if I was supposed to use [code] for that. Figured better safe than deleted
I am trying to set-up a formula that will look up two different values of a grid (x & y) and the result will be the intersection of the two values (z). If there is not a value at the intersecting cell i will have it display "n/a". I have attached an example of what i am trying to do but i have no idea how to set up the formula. I forgot to mention that the index or chart above remains constant but the lookups do change. I believe i may have figured it out. Here is what i came up with for cell C19. Should i have done anything different? =INDEX($A$2:$E$8,MATCH(A19,$A$2:$A$8,0),MATCH(B19,$A$2:$E$2,0))
View 8 Replies View RelatedIf I have, in one cell (call it D1):
EH,DR,HU
and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E
I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...
eg...
F,G,H
How to return the value of what i looking up. for example i want to find the value of A1 from a range of B1:B10 if A1 is B1 then it return the value beside B1 for example the value of B1 is C1
View 2 Replies View RelatedI have rows with text and numbers. In order to ensure that the numbers are accurate, I have a "QC formula" that calculates a check using all of the numbers from 1 row. The challenge is that the "QC formula" needs to vary depending on a text value within the row.
How can I lookup up the text value and then return the correct active formula for that row? I have too many differet text values to do a nested If statement. see simplified example below.
Condition ABCFormula' Needed based on Condition
Red123A*B*C
Blue123A+B+C
Green123(A+B)*C
Hi there, please can you help me transfer data from 1 spreadsheet to another (I have attached a file which has 2 sheets).
I am trying to populate columns G to L (sheet B) with data from column H (sheet A) - the lookup is between 'Material' & '7410','7090','7680','7060','8630','7580' on sheet B & material & plant on sheet A.
I have tried H&VLOOKUP & INDEX/MATCH but I keep returning a #N/A..