Lookup Particular Row Information If Cell Value Matches Specified Value
Feb 10, 2013
I have two worksheets, one named Data and one named Index. In column G of Data, there is an index number. In column G of Index, there is a list of special index numbers.
In columns A:F there is information about each special index numbers.
What I need is in Data, if the index number of column G exactly matches the value of a special index number in Index Column G, put the column A:F information for that row in the row with matching index number in Data.
Data Worksheet
A
B
C
D
E
F
G
1
header row
[Code] ......
I need to be able to put the formulas in A2 to F2 and just drag the formula down all ~5000 cells of Data, and only need the information put into the ~ 20 special rows, determined by the index number in column G.
View 5 Replies
ADVERTISEMENT
Mar 7, 2013
I have 2 columns,
column A has a series of 8 digit numbers (some will match some will not) sorting in A-z order from lowest to highest value.
Column B is blank at the moment.
What I want
In column B i need to add numbers starting at 1 and moving down in sequence.
If more than one rows have the same number in column A they get the same numbe rin Column B
It's really a pain or have to hand type in the numbers and I can't figure out a easy way to do this.
Attached example.
Tab 1 = Origanl Data
Tab 2 = results i'm lookin for.
View 2 Replies
View Related
Nov 16, 2006
The table i'm looking up contains 2 rows with a value which matches my lookup value, how do i get the vlookup to pick up both values?
this is what i have:
ACEUSA-0017,730.84
SISTRUINSU-0018,865.69
WARNORAMER-00212,868.59
ACEUSA-00113,948.61
This is what vlookup picks up:
ACEUSA-001 7,730.84
ACEUSA-001 7,730.84
using this formula:
IF(ISNA(VLOOKUP(I13,'May 2006 MJ'!$A$5:$B$41,2,FALSE)),0,VLOOKUP(I13,'May 2006 MJ'!$A$5:$B$41,2,FALSE))
This is what i need it to pick up
ACEUSA-001 7,730.84
ACEUSA-001 13,948.61
I do need ISNA to take care of #N/A
View 14 Replies
View Related
Aug 26, 2009
Worksheet #1:
Column "A" going down (starting at A1 to A5) I have the numbers 1,2,3,4,5 entered in each cell...
Worksheet #2:
In cell A1 is the number "1"
In cell A2 is the number "7"
I want a formula in cell B1 (WS#2) that looks for the number in cell A1 (WS#2) in the range of cells A1:A5 on Worksheet #1, and if it finds the value of A1 (WS#2) in that range of cells on Worksheet #1, it returns the letter Y... if not it returns the letter N
So my result on Worksheet #2 should be...
Cell B1 shows the letter Y
Cell B2 shows the letter N
View 2 Replies
View Related
Jan 26, 2010
a while back one of you gave me an awesome formula that pulls data from sheet 2 based on what was typed in A1 and A2 on sheet 1. Well I ran into a snag, is there a way we can add a 3rd match in the formula. See the attached file. I need to pull data from sheet 2 based on what is typed on sheet 1 in cell A1, A2 and A3.
Exp
Sheet 1, report to be printed
A1 = last name
A2 = first name
A3 = month
Sheet 2, data is held here
Need to pull data in column “M”
View 2 Replies
View Related
Jan 27, 2012
When performing a VLOOKUP, it returns the first instance of a match in the the lookup range.
So in the spreadsheet I have, I'm performing a vlookup....and finding a match...and then returning a value 1 column to the right...
What I "need" however is a formula that will accept that there is more than 1 row where the match exists and total all the values for that match.
I vaguely understand array formulas but have not been successful and am looking for some tips ...guidance etc....
View 7 Replies
View Related
Mar 25, 2012
how do i lookup values when there are multiple matches and i want to return values horizontally
eg
column A Column B
A 1
B 2
C 3
A 4
B 5
C 6
D 7
D 8
D 9
D 10
and the output should be like:
column A Column B Column C Column D Column E
A 1 4
B 2 5
C 3 6
D 7 8 9 10
View 7 Replies
View Related
Feb 13, 2008
i am trying to do a lookup on a data column (B) which contains time data on a worksheet which is a constantly growing list from a DDE data link macro- the data i want returned is in column A,
so for example
column A, column B
0.8970 13/02/2008 21:58
0.8965 13/02/2008 21:58
0.8990 13/02/2008 21:58
0.8995 13/02/2008 21:59
0.8982 13/02/2008 21:59
0.8983 13/02/2008 21:59
i am looking for the highest and lowest values where the time matches a certain hour of the day, so where another cell on the worksheet 1 equals 22, do a lookup on all the rows which is in time 22:00 and return the max no in column A (0.8983),
and the min in another cell(0.8977)
think my limited excel skills are not up to it i'm afraid despite searching through the forums and trying out a few lookup and max combos as array formulas.
View 9 Replies
View Related
May 7, 2014
I need a lookup formula that matches first three digits of column, U (153 - LINE PIPE), and match Total 13153 - LINE PIPE, 7th, 8th, and 9th digit in column C. and bring value from either column T,
The formula that I am using is not obviously not correct, I am putting it here to show what I am trying to do.
{=INDEX($T$2:$T$16,MATCH(LEFT(S2,3),MID(C2:C273,FIND(" ",C2),3)),FALSE)}
View 5 Replies
View Related
Apr 24, 2007
I have a list with 8 columns. In the majority of instances, Columns A & B will have duplicate information. I need to be able to search columns A & B & return the information from them PLUS all info contained on the same row's from columns C/D/E/F/G & H. EG:...
My column headings are as follows...
Type Region Contact Phone Email ADD1 ADD2 ADD3
In column A (type) would be the type of contact eg:business/personal/family etc.
So... If looking for family I'd need excel to show me the region, contact, phone, email, add1, add2 & add3 for each family record. Hope this makes sense!
There are over 1000 records i need excel to search through & return matches.
I'm an intermediate excel user at best so finding it complicated. Tried vlookup but that stops when it finds a match & anything else seems over my head.
View 9 Replies
View Related
Jan 28, 2009
is it possible to have a cell return all matches from a list and have the list of matches reduce as you type, then be able to select one item from the list? this is a typical feature on internet sites, but can it be done in Excel?
View 3 Replies
View Related
Mar 5, 2014
I am having trouble with my macro. I have attached excel sheet for reference.
Basically, I want a Macro that Looks up columns B & C of current sheet and matches it with table in the charges tab, and then returns hourly pay in column D.
I have created the macro but it's giving me Run time 1004 error.
Also, my formula is incorrect. Maybe that's why my macro isn't working?
2WayLookup_Macro.xlsm‎
View 4 Replies
View Related
Jul 9, 2008
Im looking for a formula that will look at the the products in column A and see if their coresponding value in in Column I is equal to "website"
I would like it to count how many website sales the product has and return the product name in order of number of website sales into the top ten list at the bottom.
I have attached a test file which should clear things up
View 9 Replies
View Related
Dec 9, 2009
I'm using a v-lookup to pull information from one worksheet to another. I know that each of the cells has a match on the second sheet, but the calcuation is resulting in a Value Not Available error. The only way I've figured out to get past the error is to go into each cell, hit F2 and then enter.
View 2 Replies
View Related
Oct 8, 2007
If you look at the attached file you will understand what I am trying to get at more. I have a download which comes from a time management system. What I want to do is have a macro which runs everytime the name dropdown box on the summary page is changed. The macro needs to lookup the correct times for that person and the day and place them in the correct order (i.e 1st clock in, 1st clock out, 2nd clock in, 2nd clock out, etc.) I am trying to find the best way to do this.
View 3 Replies
View Related
Nov 19, 2007
In one worksheet there is a code for every customer and their details e.g. Name, medication they are on, what they are alergic to etc then in another new worksheet can we just type in that code and their individual information comes up automatically?
View 4 Replies
View Related
May 23, 2006
I have a spreadsheet and im trying to create a sports simulator..
Up the top I have a drop down menu with the teams listed, now what I want to do is when I click on the team I want the players from that team listed below it..
Could someone explain to me how to acheive that?
For Example.. Where it says 'Brisbane Broncos'... Say I change that to 'Canterbury Bulldogs' when I change it to that I want the spreadsheet to update cells A5:21 with the 'Bulldogs' players..
View 9 Replies
View Related
Aug 14, 2014
I've been stuck with this issue for a few days now. I'm trying to populate another field with information already on the sheet. The data is demo data.
I've tried =VLOOKUP(C2,D55:E55,2,0) however it only returned 0
View 2 Replies
View Related
Jan 21, 2009
My company use a program called Bartender which is a barcode printing program. Bartender has the ability to extract information from an Excel sheet which is really useful and prevents repetetive keying in of information. We have found that if you simply print the Excel sheet when Bartender next uses the file it says it is corrupt and simply copying the data into a new spreadsheet and resaving it as the same name does not resolve this issue.
View 3 Replies
View Related
Dec 20, 2013
I am using a list like this.
A
B
C
D
1
Teacher
Name
Color
Size
[Code] ....
I need formulas that can evaluate the table above and provide the information below. If there needs to be multiple steps/formulas, I'm okay with that.
Two sticking points, BOLD - be listed twice with the same teacher. I don't want them counted twice in the "Size 3" column. BOLD & italicized- same student could be in two different teachers' classes. They need to be counted under both.
Teacher
Red
Green
Blue
Size 3
[Code] ...........
View 5 Replies
View Related
Apr 18, 2008
I have created a spread sheet that uses the LOOKUP function to pull information from elsewhere in the sheet and automatically fill in a cell so that I can save time.
The spreadsheet has several columns with a single word or number but the column used for the lookup contains 2 words, 1 number and a “#” sign. I have used the “CONCATENATE” function to create a column that has all the words, the number and symbol and then my formula says to LOOKUP the value in the created column and return the number associated with that LOOKUP value. The problem is it will not give me the correct value. All cells with the formula return a value from the same row in the LOOKUP table no matter what the LOOKUP value..
I have used this exact same formula (copied and pasted it) in another place in the table with the LOOKUP information added by hand, not generated with the “CONCATENATE” function, and it works fine.
View 9 Replies
View Related
Jan 10, 2008
Found several items close to this, but not exactly (at least that I could find). It's difficult to type what I need to do, but here it goes.
I have two sheets. Sheet1 has data that I want to look at in range A1:B7; Sheet2 has column titles waiting on data from Sheet1. If one of the column titles on Sheet2 is "Mike," I need to look for all "Mikes" from A1:B7 and pull the data automatically onto the other sheet. I apologize if confusing, but I tried as best I could to describe it. I have attached a small file for guidance. Using the attached file, take "Mike" for example. Under his name on Sheet 2, cell A2 would display 300, then cell A3 would display 1,000.
View 9 Replies
View Related
Apr 25, 2008
I'm trying to categorise a load of expenses against their appropriate budget and sub-budgets. I've got all the budget subcategories in separate dynamic named lists each headed by the main budget name. I want to be able to select the main budget name e.g. marketing or sales etc and then see, in the next column over, only the appropriate subcategories to select from e.g pick the appropriate dynamic list (I've used dynamic lists so I have some flexibility to add new categories at a leter date).
View 2 Replies
View Related
Mar 9, 2012
I have a graph which displays information from a vlookup.. The vlookup can bring either numbers which are in percentages format or ordinary number format.
The question I have is is it possible for the graph to automatically change format depending upon the information who is looked up..
If so how can this be done..
View 1 Replies
View Related
May 22, 2012
I have a drop down (look up list) labeled Equipment that retrieves information from another sheet in my workbook.
I have a second field labeled Equipment Sub Type where I need a drop down list based on the information retrieved in the Equipment Field.
Each item in the Equipment List Drop Down has a corresponding list in my lookup sheet of my workbook.
View 3 Replies
View Related
Jun 5, 2009
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 Related
Apr 29, 2009
I have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK
View 3 Replies
View Related
Jan 29, 2010
In my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":
1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List
2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List
3.) Report In Column D: Identify and return all unique values in "Column C" as a List.
Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"
View 4 Replies
View Related
Sep 24, 2013
I have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)
When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.
I need the text in column AQ (the monetary amount) to become red when the the opposite is true.
i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.
View 2 Replies
View Related
Sep 17, 2013
I am working a project where I need to copy/insert a Row of data onto Sheet 3 if values from Sheet2 are located on Sheet1. Here is my code so far:
Option Explicit
Sub move_rows()
Dim ws As Worksheet
Dim ws1 As Worksheet
[Code].....
View 3 Replies
View Related
