Lookup Value In Column & Lookup Value For Offset Column
Feb 14, 2009
I am trying to find a formula that would pick out a row from a matrix based on the values in the first column and then return to me each of the subsequent values from that row. I can't do a vlookup because there are a lot of blank cells and which columns have data changes. I've attached an example. The text in A1 will change and cells A3:B5 will show the corresponding values from that row along with the column title. I would prefer a formula to type in and leave in cells A3:B5 but if that can't be done VBA is okay too.
View 6 Replies
ADVERTISEMENT
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
Jun 12, 2009
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.
View 4 Replies
View Related
Jan 31, 2008
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.
View 9 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
Feb 10, 2010
Is there a formula to isolate observations in the same column (different values) and also all have the same column heading like the file attached?
View 2 Replies
View Related
Oct 31, 2009
What i am looking to do is have column D display the value in column B...if column C is not blank. Here is an example.
View 4 Replies
View Related
Feb 1, 2010
I have a table with 3 columns. See below for data (ignore dashes):
COLUMN X-----COLUMN Y-----COLUMN Z
Project 1---------21 Dec 09---------$70
Project 2---------21 Dec 09---------$85
Project 3---------21 Dec 09---------$90
Project 1---------1 Jan 10-----------$85
Project 2---------1 Jan 10-----------$95
Project 3---------1 Jan 10-----------$110
As you can see, COLUMN X contains Project Names, COLUMN Y contains Dates and COLUMN Z contains Monetary Value.
Here is what I need to do: I need a formula/function that will lookup "Project 1" in COLUMN X and find the latest corresponding Date in COLUMN Y (for this example, 1 Jan 10) for Project 1 and then display the corresponding Monetary Value from COLUMN Z (for this example, $85).
I have tried using VLOOKUP, but since the data must be sorted it won't work. I tried using INDEX(MATCH), but can't get it to work. My goal is that the cell will be dynamically updated every time a user inputs updated data for a certain project based on the latest date.
View 3 Replies
View Related
Feb 11, 2013
I have a sheet that begining in A3 and going down need to look for the First Instance of the text String "Loan Documents" and down to the last instance and select the corresponding range in column B.
Example: if the Text String appears in Range(A14:A32) I need the Range(B14:B32) to be my selected range.
View 1 Replies
View Related
Mar 27, 2009
I have a file that I would like to lookup variable row and a variable column. I have tired vlookup and hlookup but these do not work because you have to specify a given column or row versus having that column or row be variable. Is there a way to do this.
Think of a set of times tables. I would like to input 8 and 9 and get 72 as an output. How could I go about doing this?
Or in the attached file, I would like to say A and 15 and get A15.
View 3 Replies
View Related
Oct 20, 2008
I have a set of data
Col A, B, C
A = Date, B = Interval e.g 08:00, 08:30, C = Number
My aim is to lookup to A+B in order to retrieve C
I have tried and offset within a lookup and upto now have been unable to proceed, I have attached the formulae I was using;
=HLOOKUP($I$4,Sheet2!$A$5:$A$6,OFFSET(B5,MATCH(H5,Sheet2!$B$5:$B$6,0)*0,1),FALSE) - returns the date value
View 9 Replies
View Related
Jun 19, 2006
I am trying to use offset & lookup, to return a cell x rows below the lookup value. I am able to get the correct result when I use the lookup and offset functions separately. When I nest the lookup function within the offset, I get an error.
View 6 Replies
View Related
Feb 2, 2007
I have a range of numbers in row 8 all of which are unique, then another range in row 20 (there are numbers in between but I'm not worried about those). I'm trying to get a code or formula that will find a user defined number typed into c30 (which can change) in row 8, then scroll down to row 20 (same column) and sum all the values from b20 up to and including this point (along this row). The number inputted into c30 will always exist somewhere in row 8.
I can't work out if this is possible using lookup/offset/cell/sum/other functions, figure it may need VBA.
View 7 Replies
View Related
Jan 10, 2014
I am trying to find value for a particular date and then sum this value with the value from the column next to it.
I can use hlookup to get the first value but don't know how to include the value of the next column to it.
View 9 Replies
View Related
Oct 22, 2008
I'm trying to create a IF-formula that looks through a column with numbers and if i specific number (in my example the number 7) exist in the column the word PASSED should be written i the cell. I have tried to use =IF(A1:A5=7;"Passed";"") but it does'nt work.
View 2 Replies
View Related
Dec 16, 2009
I am trying to create a formula that will look up the data in Sheet 2, A3 and if found in Sheet 1 to then look up Sheet 2, C1 in Sheet 1 and then enter the data if found into C3. Is there a way to do this?
I have attached a sample workbook.
View 10 Replies
View Related
Oct 30, 2006
I have a large array of cells that go from A1 to A2500. In VB, I need to take a given date that resides in a cell, say 1/6/2006 in C2, and look for a match of it within this large array. When the match is found, I need it to look in the column to the right (IE, from A5 to B5) and assign the value there to a Cell (C1).
Could someone provide an example of the code I was use in VB for this? I'm having a lot of trouble with it.
View 7 Replies
View Related
Dec 16, 2006
I have a worksheet of data that I am trying to reference to. I will have the data as a "dump" on one worksheet, and lookup from that sheet. I've attached an example of the data dump. I need to essentially VLOOKUP the value (i.e. 054_6425) and then count over 3 columns, and down two rows. I've tried INDEX MATCH, but can't get that to work. Can you get VLOOKUP to count over and down and return a value? File "example" is attached.
View 2 Replies
View Related
Jul 7, 2007
i'm trying to return a value thats offset but the position of the value may change each time data is imported. i've attached sheet displaying problem, as i can't explain clearly. i need to lookup the value in column A (yellow), then find and return the total percentage value in column C (blue). its always 2 columns across but the problem is it could be anything from 2 to 5 rows down.
View 6 Replies
View Related
Jan 30, 2008
I am referencing a value in column A of Sheet 1. I am looking for that value in column B of Sheet 2 and would like to return the value of the cell that is 1 row below and 4 columns to the right (column F). I need to do this multiple times.
Can I use a VLOOKUP/OFFSET combo or Index/Match?
I am writing the formula in column H of sheet 1.
View 3 Replies
View Related
Feb 5, 2008
The Vlookup portion is already working but I need the value located one row below that one.
here is what I have, which return the value itself, I cant seem to incorporate the offset function ion VBA:
Cells(x + 1, y) = Application.WorksheetFunction.VLookup(TempName, Worksheets("Data").Range("F1:F100"), 1, False)
View 3 Replies
View Related
Jan 26, 2014
In my spreadsheet I have a column (C) that consists of 20 rows. Within this column a calculation is done with only one value (currency) that appears based on the calculation criteria. All other rows are 0 (currency) and the correct calculation can be in any of the 20 rows.
In an adjacent column (say E10) I want the appropriate value to be entered from column C.
View 3 Replies
View Related
Feb 9, 2010
I need a formula to lookup and retrieve data from a table of values. Given an EMP-ID in column A of the row that contains the formula and given a column name in row 1 of the column that contains the formula, I need to do a lookup. So, in effect, I am doing a lookup based on two values .. the column header and the row header.
I have attached a small model to illustrate what I need. Cell C-3 in the first worksheet will contain the formula to lookup and retrieve the value in cell C-7 in the second worksheet, based on the values in cell C-1 (column header = “Database Col 2”) and in cell A-3 (row header = “257”) in the first worksheet.
View 2 Replies
View Related
Feb 10, 2010
I use the sumproduct for the attached example. I know I have seen this somewhere on the forum where I can get a value based on a criteria from a row and criteria from a column, but I just can't seem to figure it out right now.
View 2 Replies
View Related
Nov 10, 2011
I have a list of numbers in column 'C' to lookup and if it is true, I need text "on schedule" to display in column 'D'. If it is false I need text "not on schedule" to diplay in column 'D'.
View 4 Replies
View Related
May 24, 2013
Want to type in a formula and copy it down through the column.
The formula should find the most recent final for the country listed in column A.
Country
Champions league final
Formula (Most recent final - country)
Spain
2012
?
[Code] ........
View 2 Replies
View Related
Aug 15, 2013
Worksheet 1
Food
Expenses
KFC
500
Pizza Hut
100
[Code] ......
In worksheet 2, I would like to lookup the type of food that have expenses at 500.
So the output column should look like table below.
Food
KFC
Wendy
Sushi King
Papa John
View 5 Replies
View Related
Nov 22, 2007
I have a list of values in column A (part numbers)
In columns B to E I have lists of similar values (part numbers, with row 1 representing their location e.g warehouse, office, etc).
In column F2 I would like to search for the value in A2 in the range B2:E100 and return the value in B1:E1 if the value appears... if it doesnt appear in any of the columns I would like 'not located' to appear in F2.
I've been trying to do this for a while but am making no progress at all.
View 9 Replies
View Related
Dec 12, 2008
I have data in one column which is text based (A, B, C, D, E) and in the accompanying column is a response (1, 2, 3, 4, 5).
I want to be able to find those who answered 1 and then group to letters - so if 'A', 'D' and 'E' replied '1' then I want the result of the formula to be ADE.
I have been doing this manually and just typing the results in from a print-out but am hoping I can automate and get Excel to do the work for me to eliminate inputting errors!
View 9 Replies
View Related
Nov 4, 2009
I have a spreadsheet with a list of thousands of products in Column A. In Column B, C, D, E, & F there are headers that read: Cincinnati, Dayton, Columbus, Toledo, Cleveland.
In the rows under these column headers it’s either blank or there is a 1. The value 1 represents usage of products (column a) in those markets (columns b thru f). I guess I could use a Y for yes but I used a 1 instead.
In another workbook, I have a short list of a dozen random products that I am trying to match up to the cities that use them. Is there a simple formula that would search for a specific product in column a & return the first market with the value 1 in the corresponding cell?
View 9 Replies
View Related
