Lookup Across Columns & Return Matching Detail
Feb 26, 2008
I am trying to track a score. A master capacity sheet has two columns with identifiers (upstream and downstream nodes) relative to a pipe segment. I have a separate sheet (condition sheet) with the corresponding scores. I want to enter the upstream and downstream node numbers in 2 columns (A & B) in the master capacity sheet, and based on the the upstream and downstream node numbers, have the corresponding score (from the condition sheet) on that row copied in the master capacity sheet.
View 6 Replies
ADVERTISEMENT
Mar 5, 2008
I want to write a formula so that if an item in column A = " name" and column B = " date" (name and date to be selected from a drop-down box), then it will return the adjacent cells text in column C.
For example;
ColAColBColC
nick01-Marabc ltd
mick01-Mardeliver it
dan02-Mardiy
nick03-Margo ltd
mick04-Marfreebie
dan03-Marhelp ltd
If I select "nick" and "3/3/08", then the formula will return "go ltd". I know how to do filters, my aim is to create a master sheet and then for someone else to be able to select their name and a month. The month will then update a mocked up calendar (i.e. a 7 by 5 sheet where cell A1 = Date selected, A2 = Date+1, A3 = Date+2 etc). I've attached a basic mockup of what I want to do. If I select nick and march, the yellow highlighted cells should populate in C9 and E9 with abc ltd and go ltd, the others blank.
View 3 Replies
View Related
Jul 24, 2009
In column "AT" I have a list of matchups between 2 soccer teams that looks like this
Row 1:Manchester UnitedArsenal
Row 2:Chelsea FCLiverpool
Row 3:TottenhamAston Villa
Row 4:Hull CityLiverpool
Row 5:Everton FCSunderland
Row 6:Bolton WanderersManchester United
Row 7:PortsmouthTottenham
Row 8:Birmingham CityStoke City
Row 9:WiganBlackburn Rovers
Row 10:LiverpoolFulham
In columns "AM" and "AN" I have a rating for the home and away team. So for example, AM row 3 would show the rating of Tottenham and AN row 3 would show the rating of Aston Villa.
Now what I want to do is set things up so that I don't have to enter every teams' rating in manually. I need a formula that would look from bottom to top (row 10 to 1), find the first row that contains Tottenham (for example), and then look to the right to either column AM or AN (depanding on if Tottenham is the first team in the matchup or second) and return the value in that cell.
View 9 Replies
View Related
Sep 14, 2006
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 Related
Jan 5, 2007
I am trying to track cost. A master purchase order sheet has a column with a number relative to an account. I have about 50 accounts and a seperate sheet for each account. I want to enter the account number on the master purchase order sheet and based on that number, have the following data on that row copied to the next available row in the sheet for that account.
View 9 Replies
View Related
Aug 11, 2008
Im trying to work out how to write some code to find and replace from a list.
I have key data in column P on sheet 1, and a list on sheet 2 with the same key data in column C.
Basicially, every month the data on sheet 1 changes (there are usually 100 rows of data each month) and what i want to do is, write some code to look down column P on sheet 1 and for every cell, find the corrosponding value on sheet 2 (column C). When it finds the value, copy and paste it onto sheet 3 then delete the row from sheet 2.
I hve written some code that can do the first part (copy and paste) and a second macro to do the deleting....but it is only for 1 value at a time...
View 8 Replies
View Related
Mar 7, 2014
Alright, so I need data in column A(Last names) and data in column B(First name) to match their respective names on another sheet. When a match is made, it will return column C(Hours worked).
I originally used Vlookup to match last names, something akin to =VLOOKUP(A:A,Week1!A:P,4,FALSE)
But this does not account for employees that may have the same last names. Is there a way to combine two vlookup's? Or is Index a possible solution?
View 5 Replies
View Related
Feb 19, 2008
I'm working on a excel sheet to compare data of 2 columns. One column (or array), let's say C3:C13 contains "x" in some of its cells. The other column, let's say P3:P13, contains "d" in some of its cell. What I would like to do is compare the cells of the same row, see if they contain any data and return 1.
So if C3 contains an "x" and P3 contains a "d", therefore it returns 1. If C4 doesn't have an "x" and P4 has a "d", it will return a 0.
The closest I can get to this is the following formula
=SUM(IF(AND(C3="x",P3="d"),1,0), IF(AND(C4="x",P4="d"),1,0), IF(AND(C5="x",P5="d"),1,0), IF(AND(C6="x",P6="d"),1,0)), IF(AND(C7="x",P7="d"),1,0)), IF(AND(C8="x",P9="d"),1,0)), IF(AND(C6="x",P6="d"),1,0))...
However, this formula is way too long. Is there another way of writting or simplifying this formula?
View 13 Replies
View Related
Mar 20, 2014
I have a two different workbooks book 1 and book 2 with some data.
If values of first two columns ( Column A and B) of both workbooks are matching then I want a formula that can return the value of third column (column c) of book 2 in book 1 column c.
example:
Book 1
column A / Column B / Column C
100 / 200 /
Book 2
Column A / Column B / Column C
100 / 200 / 300
now I want a formula that can return in column C of book 1 to display 300.
And the data runs across some 100 rows in both workbooks.
View 4 Replies
View Related
Jul 17, 2013
I have used the merge facility to incoporate all the worksheets into one excel files. As they all have same heading, I have sorted the data, first by their account expiry time (oldest to newest) and then by manager (A-Z)
For example, system report generated on 15/03/2013, 28/03/2013, 03/04/2013, 15/05/2013, 28/05/2013 and so on and these are sheet names too. What I want is one worksheet called report with the same heading as my merged worksheets and return values where Column C in 28/03/2013 is compared to 15/03/2013 and if the employee ID matches than return the whole row of data for the report.
the next query would then be for finding employee ID in 03/04/2013 and comparing it with 28/03/2013 worksheet and returning the matched ID in report worksheet.
This is the layout of the report worksheet.
Display NameEmployee IDAccount StatusE-mailDepartmentManagerComment
15/03/2013 to 28/03/2013
28/03/2013 to 03/04/2013
03/04/2013 to 15/05/2013
15/05/2013 to 28/05/2013
so for the above report worksheet, if employee id matches the two compared worksheet (15/03/2013 to 28/03/2013) return all value (Display name, employee id, account status, email, department, manager, comment) from the 28/03/2013 and so on.
View 2 Replies
View Related
Jan 9, 2009
I have a worksheet with 2 columns. The first column contains names and the second column contains numbers associated with the name.
How can I specify a name, go through the sheet to find all the occurances of that name and then paste all the numbers associated with that name in another worksheet in a set position.
View 9 Replies
View Related
Jun 26, 2012
Here are two sheets:
Sheet1
systemip1 ip2 ip3 ip4 ip5 ip6
system11.1.1.11.1.1.21.1.1.31.1.1.41.1.1.51.1.1.6
system22.2.2.22.2.2.32.2.2.42.2.2.52.2.2.62.2.2.7
system33.3.3.13.3.3.23.3.3.33.3.3.43.3.3.53.3.3.6
Sheet2
ip system
1.1.1.3
2.2.2.3
3.3.3.6
3.3.3.1
Sheet 1 has 7 columns(system,ip1,ip2,ip3,ip4,ip5,ip6 and ip7)
Sheet 2 has 2 columns (ip,system)
I have to fill column "system" in sheet 2 with "system" listed in column 1 of Sheet1.
In other words look for "ip" in Sheet2 in 6 columns of Sheet1 and return column 1 of sheet1 as value.
View 5 Replies
View Related
Dec 3, 2012
I am trying to look in B1:B100 for the Cell that matches A1 and C1:C100 for the cell that matches A2 then return the value from D1:D100 that meets the 2 criteria.
View 6 Replies
View Related
Nov 30, 2009
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.
View 9 Replies
View Related
Sep 21, 2006
I've been working this for ever and can't seem to figure out the best way to go about it. I have attached an example sheet. All I need to do is figure out the Dept #... which is listed in Row 1, Column F:H. I want to match the project numbers and then return the AA, BB, or CC in Column B.
View 9 Replies
View Related
Mar 29, 2008
I have a data range set up in a step chart formation. This means there is a "Name" to the left and a time line of "values" to the right in quarterly increments (3 months at a time). Row 1 has the first month of each quarter and should be used to find the start date. Row 2 has the last month of each quarter and should be used to find the end date.
I need to find the first value from left to right and return the date directly above it in row 1.
Then....
I need to find the last value in the step chart and return the date directly above it in row 2
Example
Start Date..A...B...C....D...E...F
End Date....U...V...W...X...Y...Z
Value-1...........5...2....7........
Value-1
Start Date: B
End Date: X
Duration: = B - X
The reason i need this information is to create a Gantt Chart.
View 9 Replies
View Related
Dec 11, 2012
I'm trying to find a way to:
Use a referenced lookup value from sheet "A", to return values, from several columns in sheet "B"
Things to note:
a) The lookup values sometimes repeat. I need all the associated values with each repetition as well.
b) The lookup values in sheet "A" are a comprehensive list, sheet "B" also contains some of these values but not all. Essentially, what I need to do is find a way to lookup each value in an account numbers column in sheet "A", against a different account numbers column in sheet "B".
If that value occurs in sheet "B" I want it to return the values from Columns X, Y, Z, (I want these values returned in sheet "A".
If that value does not occur in sheet B, the corresponding cells should remain blank.
If the lookup value occurs multiple times, I need all the corresponding values from each of X, Y, Z columns.
View 2 Replies
View Related
Jun 12, 2007
I have a very large spreadsheet of customer information(I call it the master spreadsheet). Each row contains only 3 things: Account number, product bought, Price
Later I receive the money from the customer for that product(the pay sheet) that contains the exact same thing in the same order: Account number, Product bought, Price paid.
What I'm trying to do is compare the two spreadsheets so that when i receive the pay sheet of cusomters who have paid with the amount it will deduct it from the master sheet.
So it should compare account numbers when it finds a match then it should subtract the amount paid (column C) from the master spreadheet price column(column C also).
sometimes customers don't pay the right price so it has to be a subtraction so I can see if it was over paied, underpaid etc.
Right now I'm still doing it manually combining the two documents sorting it by account number and checking for matches in column A (account number).
View 5 Replies
View Related
May 14, 2014
I have a grid that I need to lookup and return the project hours for each system from the data set. The grid has the projects listed in the rows and the systems listed across the columns. The data set has a list of each projects systems hours. I am having problems with the formula when the data set has multiple records (multiple systems) for each project. It seems very simple, but I just can't get my arms around it. Please see the attached for example.
View 7 Replies
View Related
May 15, 2009
I have a need to match a reference cell to a list of cells. How do I get it to match the exact contents of the reference cell, not just whichever is closest?
I'm attaching the sheet, it's important that the contents of A13 match a string in the AG column exactly or return a null or "none" or whatever.
View 3 Replies
View Related
Aug 19, 2006
see teh attached file, you can see on the file. Worksheet B of B62, I typed 15254", it col name "yarn count" and the product code you can see is Q921 at cell A62. Worksheet A, you can see F2 is Q921, col name is "product code". Now I just plugin a vlaue B62 ='15254' and I want it shows on worksheet A - G2 but remember it must match the product code of worksheetB - cellA62 'Q921' and worksheet A cell F2 'Q92' because next time user will plugin a number ie.1111 on B164; where product code of worksheet B - cell A164 must be match to worksheet A - cell F3, but it should show the value 1111 on cell G3.
View 2 Replies
View Related
Oct 24, 2006
I am looking for a way to add cells to a master workbook from matching cells in various other workbooks. The master will have the complete list of store numbers. The other files will just show the store number that had returns. I need to add a new column to the master workbook for each of the other files. If that makes sense?? So the master report header row will have: storeno,storename,3-1-06,3-15-06,4-1-06,4-15-06, etc..... Then column a will be a listing of the store number 1 -200.
View 2 Replies
View Related
Apr 7, 2014
I 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.
View 3 Replies
View Related
May 2, 2014
how to figure it out this lookup problem (lookup using partial string of match)...
View 5 Replies
View Related
May 26, 2009
I have an items list on page 2. On page 1 I want to create a drop down menu in C2 that references the list on page 2. Then when I choose an item, I want the additional information in cells D2 and E2 to come in automatically. The list on page 2 will be expanding daily, and when I expand it I can simply insert a new row so I correctly alphabatize it instead of constantly having to resort it.
With that said....
1. How do you put in the drop down box that references information on a different sheet?
2. What is the best way to reference the information in my ever expanding situation: Index/Match, Vlookup, Lookup, or something I havent mentioned?
View 14 Replies
View Related
May 16, 2013
I'd like a formula that'll return the column header by matching a lookup value with a table in the second sheet.
eg: sheet 1
Name
Cell
Region
John
111-2222
[Code] .......
The formula should match the name in A2, John, with value from the table in sheet 2 and return the correct region, this case North.
View 1 Replies
View Related
Sep 21, 2013
I want to find the corresponding Disc Codes from a list and copy them in the DiscName column in the summary sheet. some lab names will have more than one Disc codes so when I run the macro it should bring up all the relevant Disc Codes matching with the Lab name to DiscName column.
Col 1 col 2 col 3 Lab name Disc Name(say abcd) xxxx yyyy zzzz ppppand
The list looks something like this.
Col 1 Col 2 Lab name Disc nameabcd xxxxxabcd yyyyyabcd zzzzzabcd pppppbcda
qqqqqbcda rrrrrbcda iiiiibcda jjjjjbcda kkkkk
View 1 Replies
View Related
May 9, 2014
Code:
Private Sub GetColData()
Dim SrchRng As Range
Dim SrchVal As Range
[Code]...
And it is also possible by using Column Number to retrieve the data SrchTxt, which is in a cell in the Row, in which "Roy" is found. This Row in this example is Row 5. Column Number may be anything within the search Range. Column Number is 3 in this example.
In Sheet1 "Poet" is in C1. (SrchTxt)
How to retrieve data of the cell in Row 1 in the column where "Roy" is found?
View 1 Replies
View Related
Dec 10, 2006
I am attaching a sheet wherein I have tried to match two variables and to return the appropriate value. But I find the formula returns the value which one cell below the correct answer.
View 6 Replies
View Related
Apr 7, 2008
I have a userform with several textboxes. I am trying to get one of the textboxes to auto populate based on the entry in another textbox. example: I want to enter a customer ID in one box and then the customer name to automatically appear in another box. The customer ID and names are stored in another workbook.
View 6 Replies
View Related
