VLookup To Search Range On Different Sheet And Return Appropriate Corresponding Cell
Jan 22, 2014
I need to lookup to search a range on a different sheet and return the appropriate corresponding cell.
Basically its if a1 is found in the other sheets range a1:a100 then return the corresponding Bcolumn value from the different sheet.
Formula
=VLOOKUP(A2,'All Users'!A$2:A$100,'All Users'!B!2:B!100)
Example of 'All Users' Sheet
A B
STAFFIDSTAFFNAME
24177John
10487Paul
20031George
84087Ringo
85772Pete
24485Stuart
3829George
51836Yoko
View 3 Replies
ADVERTISEMENT
Sep 24, 2013
Basically I have three sheets. MAIN, Sheet 1 and Sheet 2
Sheet 1 and 2 are in the same format
A3 down is a list of country names and then B3:I71 contains the data im interested in.
I've been trying to create a function that looks at B3:i71 to see if any cell in that range contains a value greater then $0.00. If it does then the row that contains the cell with a value greater then $0.00 (between col A to K) should be copied to sheet MAIN from cells B3 down. This should ultimatley produce a list of data for any row containing a value greater then $0.00. This process should then be repeated on Sheet 2 and should join the list below sheet 1.
View 3 Replies
View Related
Oct 7, 2008
I'm looking for a formula to search for a value in the SECOND (instead of first) column of a table array and return a value in the same row from FIRST (instead of the same or another) column in the table array. Formula would be searching for the unique production order number in the column B and return production line id from the column A.
A1 production line_id
B1 poduction_order_number
A2 L1
A3 L2
A4 L1
B2 505212
B3 504234
B4 505663
I was trying vlookup(504234;B2:A4;2;0) to make formula go search from right columns to the left but then excel is switching the search table to A2:B4 and gives #N/D!
One remark-there is no possibility to switch these 2 columns to simplify. I have to leave them as they are.
View 2 Replies
View Related
May 4, 2013
Create a macro button in 'Spreadsheet 2' that searches 'Spreadsheet 1' for updated information specific to a certain criteria and adds it to a new row in 'Spreadsheet 2.'
There are three sheets
Prospects (where all original data is entered)
Actions -Bob (Bob's new Prospects are added to this sheet)
Actions -Frank (Frank's new Prospects are added to this sheet)
So in this example Row 6 in Prospects (Constituents, Rating, Manager and Solicitor would be added to ACTION - Bob's sheet on Row 5
and
Row 8 in Prospects (Constituents, its Rating, Manager and Solicitor would be added to ACTIONS - Frank sheet on Row 5
Here is the example spreadsheet - Prospects and Actions.xlsx
View 3 Replies
View Related
Mar 26, 2014
I have a sheet named formula the column highlighted in orange will be used in searching to the other sheet which is the table, I try to used the formula VLOOKUP but it didnt work.
View 5 Replies
View Related
Apr 3, 2008
1. can i limit vlookup on sheet2 to search only a certain range of cells on a sheet1.
example, I want vlookup to look in sheet1, colum A - Y but only look between rows 1 and 100
2. If 1 can be done, and i happen to add a row in worksheet1 (making it 101 rows vice 100) will the vlookup code on sheet2 include that 101st row or do i have to redo the range manualy.
View 9 Replies
View Related
May 7, 2013
I have a workbook containing multiple sheets for documents issued to manufacturers. I have a summary sheet which contains all of the documents and I would like to be able to look up a document number against all the sheets, and return the name (or names if multiple finds) of the sheets containing this document.
I have got close to solving this by finding another post[URL] .... but I believe this doesn't work because it searches on values rather than matching text.
I have attached a simplified example file with the sheets to search being Fab 1, Fab 2 and Fab 3, and the formula need to go into column B of the Main Sheet.
Example for Ozgrid Forum.xlsm
View 6 Replies
View Related
Jul 22, 2014
I've got an Excel document with over 323 entries and I need to search the data to find where ever a certain keyword is mentioned and copy the entire row into a separate sheet within the same workbook. However, I have a list of around 323 keywords and there is AND condition as well.so doing this using Ctrl+F would take some time.
The data is in Sheet "Training Dataset" and the particular column that needs to be searched is Column "A" . "Training Dataset" is the sheet where I would like the found results to be copied to in the column B and C. The list of Keywords that need to be searched for are located in Sheet "Keywords" , starting from Cell C and D.
View 12 Replies
View Related
Feb 16, 2008
i have a workbook with several sheets in it. i would like to make a userform were i could put a number in a text box eg E045698.then use a command button to search all the worksheets for that number and display the sheet number where that number is in another textbox.
View 5 Replies
View Related
May 12, 2014
I want to search within cells A4:F25 for "B71 - Brown" and show the number that is in the cell below this text. If there is multiple cells with "B71 - Brown" I would like the cell to show the total.
So for example,If the formula was looking for "B71 - Brown" in the cell it would show 8136. (A13+A21+D25+E17). If i changed it to "Lavender" it would show 2380. (C5)
Also this data is linked from several other sheets and the values i search for will change and will need to update.
View 1 Replies
View Related
Feb 15, 2008
I have a workbook that has rows of data starting at A5:G5 seperated in groups of 25 rows.
Example: A5 is numbered 1 and A6 is 2 A7 is 3 and so on to 25 then skip a row and start numbering again to 25.
What this is for is employees are placed in groups of 25 to be eligible for an award for safety.
What I want to do is Range G5:G650 is to be a place where an "X" is placed beside an employee who has had an accident then Range H5:H650 would return "Employee had an accident" I already have this part working.
Then I want to code a subroutine that would search for the "X's" and return the entire row data for each name in that group of 25 on a seperate worksheet.
This would then be printed so we would know which group's to exclude.
I know how to make it return the row of data containing the "X" but how can I make it grab the entire group?
There will be multiple groups of 25 each and each time an "X" is found within a group create a new worksheet for that group.
650 employees / 25 = 26 groups
View 14 Replies
View Related
Oct 11, 2011
I have data that has months in it that are spelled wrong and in different formats etc. I need to be able to search the range of data I have created in a separate table in order to return the correct abbreviation into my spreadsheet. See below:
Need to be able to search this range (A1:E12) and return the corresponding horizontal value in the last column (Jan, Feb, Mar)
JanuaryJanuraryJan-11JanJan
FebruaryFebruraryFeb-11FebFeb
MarchMarchMar-11MarMar
AprilAprilApr-11AprApr
MayMayMay-11MayMay
JuneJuneJun-11JunJun
JulyJulyJul-11JulJul
AugustAugustAug-11AugAug
SeptemberSeptemberSep-11SepSep
OctoberOctoberOct-11OctOct
NovemberNovemberNov-11NovNov
DecemberDecmberDec-11DecDec
I am currently trying this, but its not working! =IF(F2="","",VLOOKUP(F2,Dates!$A$1:$E$12,5,TRUE))
View 9 Replies
View Related
Nov 7, 2013
I use the following formula in excel 2007 (and it's working perfectly in 2007) but this formula does not work if I work with the same file in excel 2003 ...w why and what I have to adjust?
=IF(SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B))=0;"not found";INDEX('sheet 2'!D:D;SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B));1))
(I'm working with the dutch excel version so it might be that ";" must be ",")
View 6 Replies
View Related
Sep 4, 2009
I WANT it to search all of column A for the highest number in the range (which will change daily to a new high number above the highest currently), and return the value in column 4 (column D) that corresponds to that value from column A. This is what I have, but it is not working, it is giving me the number (1) in Cell A800 (which is the lowest valued number in my range. The numbers range in value from 1 to 762 with none repeating (they are sequential). The formula I have is :
=+VLOOKUP(MAX(A76:A2000),A76:I2000,4)
View 2 Replies
View Related
Jun 21, 2007
I can solve my problem with a pivot table, and with VBA easily...however, I'm interested in knowing if this can be done with formulas (array formulas using index/match I'm assuming).
Goal: On sheet1 I have one column with products, then the column next to it will have an "Y" in it if the product is to be selected (blank if not). On sheet2 I want to create a list of the products that were selected (having the "Y"). The only thing stumping me is that I do not want spaces between the product list on sheet2...just a nice continuous list. Example:
Sheet1
cup Y
bowl Y
spoon
fork
knife Y
Sheet2
cup
bowl
knife
..not..
cup
bowl
knife
View 4 Replies
View Related
Jan 10, 2007
Q1: In the range for the lookup I'm performing, the column with the possible match is the 2nd column. I've tested my function with this range, but it fails (range is $A$x:$K$x, with possible matches residing in column B). If I change the range so the 1st column has the desired data ($B$x:$K$x), it works. Can I modify the function to search using the 2nd column so I don't have to rearrange the columns in my worksheet?
Q2: Can I return an entire rold of data? If yes, how? I only know how to return one cell.
View 14 Replies
View Related
Nov 21, 2007
I have a workbook with two spreadsheets in it. On the second sheet there is a large table, which column headings are months and years (e.g. Jun-07 Jul-07, Aug-07, Sep-07....). I was trying to write an excel vba code that would search the first row (column headings) to find the current month and year, and copy the corresponding column along with two previous columns (months) to the first sheet. I would like to have a code that will be able to do this in Jan-08, Feb-08, or Jan-09 as well.
View 2 Replies
View Related
Aug 31, 2007
I wanted to lookup value A5, and return all columns in the range A1:D5, I could use the formula =vlookup(A5,$A$1:$D$5,2,false) and copy this formula 3 times changing the third parameter 2, to 3 , 4 and 5. i.e 4 VLOOKUP formulas.
I could, instead, convert the VLOOKUP into an array and enter it CSE i.e =vlookup(A5,$A$1:$D$5,{2,3,4,5},false). This returns the right answer but "appears" to have only used 1 VLOOKUP.
What if this was extended to say 26 columns so instead of "hard coding" the array constants I used =vlookup(A5,$A$1:$D$5,COLUMN(B:AA),false). Is this better than 26 indivisual VLOOKUP's in term of speed?
View 9 Replies
View Related
Jan 19, 2014
Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.
The attached spread sheet has performance data for a group of employees.
What I need to do is find a particular employee then return a value for one of the category's.
For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.
Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.
Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.
Maybe Offset-Index-Match is not even the way to go here?
View 7 Replies
View Related
Nov 27, 2013
I need to do a vlookup of the values of Sheet 1 Column A (Ref) in Sheet 2 Column A (Ref).
The results will be the concatenated values of Sheet 2 Column B + C + D + E to be displayed in Sheet 1 Column B (Address).
I'm not sure how I can do this using formula.
I've attached a example.
View 6 Replies
View Related
Jul 20, 2014
I have a large Item sheet that also functions as an order sheet so I want another sheet to return only items actually ordered. What is the correct vlookup formula for this situation?
in sheet #1, Column A has the number of orders needed (to be filled in), column B has the name of the product, column C has the price of the product. I want to look through column A after it is filled in and return the value of all three columns only if column A has a value larger than 0 into sheet #2 in the same workbook.
View 12 Replies
View Related
Jul 11, 2014
Data set
Column A Column B
PriceCode1 5.36
Sheet 2 Data Set
Column A Column B Column C Column E
PriceCode1 5.02 10.01 313
PriceCode1 10.02 50.01 314
PriceCode1 50.02 100.01 315
Formula on Sheet 1:
{=INDEX('Price calculation'!$E$1:$E$70,MATCH(C3&D3,'Price calculation'!$A$1:$A$70&'Price calculation'!$B$1:$B$70,1))}
The issue I am having is that the value isn't an exact match so it is returning line 3 with a value of 315 rather than line 1 and a value of 313. What am I doing wrong?
View 1 Replies
View Related
Dec 9, 2011
I am trying to do a vlookup to 1 sheet based on customer number and return price. If no customer number than I want to do a vlookup based on dunnage number to return the price. This is what I have for a formula but it never returns a value if one is found in the first lookup.
=IFERROR(VLOOKUP(C4,'Dunnage Exception'!A:C,3,FALSE),IFERROR(VLOOKUP('Raw Data'!D4,'Dunnage Master'!A:C,3,FALSE),"0"))
View 2 Replies
View Related
Jan 25, 2010
I am trying to use a VLookup to pull a value out of a range and return my intended data. See attached spreadsheet. The data I'm pulling from is in the top chart. The bottom chart, in the factor column, is where I want the data to pull to. Ex) If the "Months of Development" column in the bottom chart =13, I want it to pull the factor value from the top chart where the month is 13. The problem is that the top chart has a range of values in the "Month Range" column of 13 - 24. I don't know how to get the VLookup to recognize this and pull the same factor into the chart below whether the value is 13 or 24. Maybe VLookup isn't the best option here.
View 2 Replies
View Related
Feb 18, 2010
I have a large table in the final tab of my excel book. This table contains data such as Company name, contact details, etc but also a date which I must contact them on (this is nothing sinister, just a curtsy call following a job ).
The problem I am having is that I am running a vlookup on the first tab on a cell referring to todays date. This only brings shows one record which is the first occurrence of this date. This isn't very useful to me
The problems I need solving are:
I would like to be able to vlookup a range of dates (i.e. 7 days before or after todays date) - how do it do this?
I would also like to be able to return multiple results (i.e. if there is more than one company within that date range I would like them to show).
View 9 Replies
View Related
Jun 18, 2014
I am trying to search a sheet for a given name and return the total point for that marking period. The main problem i am having is that the names aren't in the same cell on each sheet. It is a list of students and each sheet represents a marking period.
I have attached the file
View 3 Replies
View Related
May 12, 2009
I have a monthly time sheet. The times are entered in cells C8:F68.
When some one has a annual leave day or a bank holiday they enter A/L or B/H.
I have a summary sheet and have placed a cmd button on it to hopefully search the sheets that are months or the year.
I know how to search the sheets and increment through them and not the 'Summary' sheet.
What I need to establish is how to place in my code the actual search criteria and also limit the range to C8:F68
On top of this I need to total each occurance of A/L and B/H and display them in two cells.
I have tried to do a search of the forum but with no luck.
Like I say I have managed to understand how not to select the 'Summary' sheet when incrementing through the sheets but its just the search for the A/L and adding them up!
View 12 Replies
View Related
Sep 29, 2011
I get new worksheets on a regular basis which are of the same format (same columns with headers) and I have to perform both vlookups and sumifs on them. Their are about 12 different tabs I perform the vlookup on based a vendor name in A2 and its corresponding tab. I am tired of typing the same thing over and over again.
What I'd like to write is a Control Panel with all the equations so I can just copy/paste them in when I get a new sheet.
How can I write a vlookup which would search for the value which is one cell to the right of where I put the formula in?
Similar to:
=vlookup(current cell over one, 'vendor1sheet'!A:C,3,0).
I would then want to copy it down the worksheet. Currently type in column E everytime I get a new sheet:
=VLOOKUP(F2,'Vendor1'!A:C,3,0)
The catch
Column A has the vendor name, there are about 12 vendors, each with a different tab with related data which I am searching through.
Ultimately, I'd like to have an equation that would do the vlookup (and sumif) as described above and also use the vendor name in column A to look on a particular tab.
Something like:
=vlookup(one cell to the right,'VendorTabFromVendorNameInColumnA'!A:C,3,0)
I was going to use nested if statements on a control panel sheet with a column for the vendor name and a column for the vlookupformula and than paste a single formula all the way down column E:
In column E, every row:
If(A2=vendor1, 'controlpanel'!vendor1equation), if(A2=vendor2, 'controlpanel'!vendor2equation), etc.
Again, the vlookups would all check for one cell to the right of where this nested if statement was (located in column E, looking in F).
View 3 Replies
View Related
Jun 25, 2014
I have a formula which I took from another post to return a particular value in a specified cell as follows;
=INDEX(A1:E9,M3+MIN(IF(A1:E9=K3,ROW(A1:E9)-3)),N3+MIN(IF(A1:E9=K3,COLUMN(A1:E9)-1)))
However I don't understand what the -3 and -1 does and I believe it is these entries that are causing the error.
See attached file : TEST.xlsx
View 13 Replies
View Related
Dec 8, 2009
If I have a 'key' value in a cell in one sheet, i want to use that value to find the cell in another sheet containing the 'key' and return the row number of the cell, if more than one value then I would like to be able to loop through all the rows containing that 'key' value returning the row number of each hit, kind of a programmatic version of vlookup?
View 8 Replies
View Related
