Lookup In 2 Columns: Use A Function That Brings The Value Of C1 In Sheet 2
May 20, 2009
In sheet1 there are three columns
A1 : PO Number (for example 111001)
B1 : PO Line (for example line # 91)
C1: Amount ( USD 50.00 )
In Sheet2 there are three columns also
A1 : PO Number (for example 111001)
B1 : PO Line (for example line # 91)
C1 : Account Code ( For example AA.00.000,725001)
Now, I would like to use a function that brings the value of C1 in sheet 2 ( The Account Code) to d1 in sheet1 taking into consideration that the PO number may repeat but the line numbers is not repeating for the same PO. Attached is a sample for better illustration
I have read through other similar topics and tried using the index and match function to lookup but been unable to make the formula work.
I am trying to use the lookup function to pull data from a table using criteria in two columns and one row. I have attached a spreadsheet for example.
I want to use criteria of Day (eg . 1), Total height (cm), and jar # (eg. 1). I inherited this table from a previous coworker and don't have time to organize the data better.
In the spreadsheet I want to return the value of 8.8 (highlighted in yellow). The criteria I want to use to lookup the value are highlighted in red.
I tried to write a function so that it would search for a word from Column C in Column B, and then when it found it, give me a a combined result of Column A and B. For instance. I lookup up C1 (Goja Apple) in the range of all of column B; the function finds it in B3 (Goja Apple); then it returns the combined result A3 and B3 (Ca23 Goja Apple). I tried the =lookup, but I think the first column you select must be in ascending order, and that wont work for me.
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.
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?
when i position the cursor over a cell, i get muliple cells highlighted. i enter data and it is entered in the cell i want but the others being highlighted is confusing. i checked and i am not in extended mode.
cell AT7 is a formula which brings back a numerical value i.e. 15.
in cell AS7 i need a formula that displays the value of cell AT7, as long as that value is less than 15, if the value is 15 or higher then 0 should be displayed.
I dont know why i cant get this, seems like a simple IF statement to me, but who knows.
Once this formula is done i need to drag it down about 2500 cells and autosum to get a column total...
I have several thousands lines of data....much of the data is the same, 2-5 rows per person, but at the last two columns is different numbers/totals....I'd like to get those all into their own column so that each person has one row with all the data...I USUALLY would sit and sort by each total, shift them over to the right into their own columns, then sit and shift them all up to one line...but thats a carpal tunnel project and i know there must be an easier way....subtotalling brings the numbers down to one line once I have shifted each one over, but not the rest of the data....
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age ------------------- Dog - Rover - 3 Goldfish - Tom - 1 Gerbil - Chewit - 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.
I have a work book with 3 sheets. Sheet 1 is the main sheet and sheets 2 and 3 will use (I hope vlookup) to update 3 columns from info in sheet 1. my attempt at a vlookup call in sheet 2 is: =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
my understanding is that: - $b3 is the cell in sheet 2 that will be updated as a result of the vlookup call. - [master_AoJ_2.xls]Sheet1!$B$3;$B$65 refers to range $B$3:$B$65 on sheet1 of master_AoJ_2. it does not work... infact I get nothing at all. when I type the call into cell $b3 of sheet2 excel thinks it is stariaght text. this is how I coded the function call =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
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.
I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.
I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.
I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".
Is it possible to set up a lookup function with two lookup values? For example, say I have a list of items such as:
1 A 14 1 B 22 2 C 84 4 D 25
I'd like to have the lookup go to the above table and find the number 1 and the letter B and return 22. I can't seem to visualize how to make this work.
i'd like when i lookup abt specific in range (4 Columns is my lookup range) get the results in one column with skipping blanks Cells "Note" Blanks cells it contains fromulas with "" so it blanks but have formulas ....
In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab)
what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table RANGE = Data!A2:K255
the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit)
is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset
after some MASSIVE googling, i have stumbled accross this
B1 = Search box (txt field)
A6 (which will be a hidden column) contains =MATCH($B$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number
A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6. this is supposed to look for the next row number which contains a match and provide that row number
and througout my other columns, i have B6=OFFSET(Data!$A$1,A6,1) B7=OFFSET(Data!$A$1,A6,2) B8=OFFSET(Data!$A$1,A6,3) and so on
2 things i cannot recitify..
1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats" 2, it comes up with multile .. irrelevent results.
see attached workbook. I want VBA to insert an index/match forumla on sheet 1 to lookup a value from sheet 2. I don't want it to specify a range though. I want VBA to look to see if there is data above and to the left of the cell and if it is true insert the index/match formula. Then it won't matter what row or column I put the headings in.
I am trying to do a (what I thought was a) simple lookup involving 2 columns. Pls see attached example. I am trying to find the "?" values, by matching column A&B of the input to the Data columns and returning the $$$ column.
I thought this wouldve been a piece of cake but its got me stumped!
Can I get all the information from Employee & Benefits tab to populate in the 401K worksheet. All information is on these two sheets except status, plan entry date, and 2009 compensation.
True under Employees tab means that a person is still active and I would like for that date to populate the 'Hire Date' column; whereas, 'False' represents a person is termed and I would like for the false date to populate under termed. Can you use the social security number if its a V Lookup because I have thousands of rows and multiple people with the same last name.
I would like to do is reference a table of data using the Vlookup function but add all of the results together. I have a table where I keep track of orders being distributed, and in an inventory sheet I want it to reference that table based on the serial numbers entered and total the results.
A B C D E 1 DAY 1DAY 2DAY 3DAY 4 2 AAA1638 3 BBB7325 4 CCC3847 5 DDD5562
This is what I have. Now I want to see, for each day which has the highest value. so it should return BBB for day 1, CCC for day 2, DDD for day 3 and AAA for day 1.The formula I am using:
Tell you what, it is giving me correct result for column B,C,D but not for E. The formula for column E should return AAA, but for some reason it is returning DDD.
I just wanted to use lookup function in my code. But I had to re-write my for..next statement 8 times. How can I state it in one single lookup function used in a for..next statement. Here is my code
Sub iteration(r) Dim i As Integer Dim j As Integer Dim x1(4) As Double Dim arra(8, 2) As Double Dim arrb(8, 4) As Double r(1) = Worksheets("sonuc"). Range("j4").Value r(2) = Worksheets("sonuc").Range("j6").Value Dim myRange As Range Set myRange = Worksheets("giderler").Range("b6:b11") x1(1) = Application.WorksheetFunction.Floor(r(1), 0.5) x1(2) = Application.WorksheetFunction.Ceiling(r(1), 0.5) x1(3) = Application.WorksheetFunction.Floor(r(2), 0.5) x1(4) = Application.WorksheetFunction.Ceiling(r(2), 0.5) For i = 1 To 4
I have 2 worksheets, 1 containing a list of 5000 products and 1 containing a list of 3 columns with classifications in them (about 300 classifications). for example worksheet 1 has 5 products
Coke Volvic Mars Bar Cornflakes Bags Maltesers
The 2nd worksheet has 3 columns with the following info Column 1 Column 2 Column3 (Catagory) (sub Cat) (Sub Sub Cat) Minerals Soft Drinks 2lts Minerals Water 1lts Sweets Choc Bars Std Bars Sweets Bagged Lines Std Bags Grocery Breakfast Cereals
I want to be able to add a classification to the products in worksheet 1. For example to be able to choose a dropdown that has a list of each classification and after coke select from the "Catagory) drop down, then select the "Sub Cat" drop down and then select the "SubSub Cat" dropdown.
Product Catagory Sub Catagory SubSub Catagory Coke Minerals Soft Drinks 2lts Mars Sweets Choc Bars Std Bars
As I said I have to do this manually for about 5000 items so I need to be able to do this as fast as I can. Can you use the lookup fuctions or something like that?
how to fix the REF error in this formula. =IF((OR(G3="Hour (Truck)",G3="Hour (Truck + Dog)")),(VLOOKUP(G3,Rates!$A$186:$B$187,2,FALSE)),(IF(F3="Hanson",3.5,(IF(F3="Lakes",10,(IF(F3="NO",(VLOOKUP(E3,TonnesRates,MATCH(B3,Rates!$B$110:$D$110),FALSE))))))))). I have tried to attach the file with the minimal information in it to make it small enough but I can't get it below 48kb.
I am creating a spreadsheet to keep track of money and grouping them into categories. I have a column to group a purchase into a category (column E) and the amount spent in column F. I want column T to add up all of the purchase that were made according to the category in Column E.
