# Looking For Value In Column Then Returning Value From Different Cell In That Row

Apr 23, 2014
Basically, I want to use look in one cell to get a value then in a table look in a coloum to find the same value then return a value from a different column in that row.

This is to organize groups of children participating in an event.

See the attached spreadsheet for details.

Groups example.xls

View 1 Replies
ADVERTISEMENT
Jul 16, 2014

I am looking to write a formula that will return the row or column number of the cell that contains a particular text within the string. I know it can be done with script, but need it as a formula. Also, to add another level of difficulty, I'd like it to be a traditional formula, no special keystroke to activate. It will be used within a much larger formula.Ex. If G5 has the string "CURRENT DATE" in it, and I look for "DATE", the formula would return 7 for the column or 5 for the row.

View 4 Replies
View Related
Feb 17, 2009

If I have a column full of data is there a way to copy every nth row to a new column? For example, I may have dates and corresponding days of the week. Can I copy the date of each tuesday (i.e. every 7th row) to a new column?

View 2 Replies
View Related
Mar 22, 2007

since my previous post got a little complex I will make it easier so I can continue puzzling myself.

I have a value "10:00"

and a table:

00:00 08:00

08:01 12:00

12:01 14:00

etc

Now I sue LOOKUP("10:00",A1:B3). This will return "12:00"

But I would not want it to return this VALUE but want it to return ROW or COLUMN. I tried ROW(LOOKUP.....) but this doesn't work.

How do I look it up and then return either B, or 2 (in this example)

Once I have this I combine my 3 way lookup by combining my new found knowledge

View 9 Replies
View Related
Apr 6, 2007

I'm trying to return the # month in which a particular event occurred.

Example:

...1 2 3 4 5 6 7 8 91011 12 13 14 15 ....

...J .F M A M J J A S O N D J F M .....

1 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0

2 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0

3 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0

4 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0

5

.

.

.

.

I need a formula that will tell that for Item #1, Value 1 occurred in month 11, and #2 was month 8, etc. I would have thought that this is common, but I searched the postings for a long time. Maybe I'm over-complicating?

View 9 Replies
View Related
Jan 6, 2013

I would like to have values from every 10th row in the table.

So I want to have B2, B11, B21 & so on...

View 7 Replies
View Related
Feb 5, 2013

Why is lastRow always returning 1? Even after adding values to worksheet and running macro - its always returning 1 for lastRow.... Am i missing something here? Do I need to refresh worksheet or something? I just dont get it :/

.Range("B:B").End(xlUp).Row

Code:

Option Explicit

Private lastRow As Long

Private curRow As Long[code].....

View 7 Replies
View Related
May 12, 2014

In the attached example I paste in a report from another system into Sheet2 (rough data layout in example).

In column B in Sheet1 I have tried using Match and Index formulas to try and return only the Local Shipment figures from on Sheet2 for each staff member.

The issue i'm having is that the data in Sheet2 can change based on how long the report is run. There is also no way I can change the way the raw data is set out in Sheet2 as it's an external system.

Basically i'm looking for a formula that isn't locked down to certain cells and can work with data moving up and down columns at different stages. The key data never changes from column A and F in sheet2. But what does change is whether each person has one or both of Sales & Local Shipment data in the reporting. This obviously changes based on what is completed each day.

So I need a formula that matches the persons name in column A in Sheet1 with Column A in Sheet2, then looks for the first mention of 'Local Shipment' below it and returns the cloumn F value which is in the same row as 'Local Shipment'.

View 3 Replies
View Related
Aug 7, 2014

I have a table of data-

IDDescriptionDatePax Count

316COACH01/07/20135

323COACH3/07/201410

324COACH18/07/20141

325COACH20/08/201420

333COACH21/09/201445

345COACH22/09/20143

351COACH23/09/20143

362COACH21/10/20142

371COACH22/11/20142

394COACH23/12/20140

416COACH23/12/201418

422COACH23/12/201439

Example, the real data is about 38,000 entries. I want to build a query to return the value of Column D (pax count) based on the values of Columns A (ID) and C (Date)

This would feed into a second, ID specific table, like this-

ID 536 Bookings JULY 2013

1/07/20132/07/20133/07/20134/07/20135/07/20136/07/20137/07/2013

(value) (value) (value) (value) (value) (value) (value)

SUMPRODUCT, usually my mainstay, is for some reason letting me down, returning a value of 0 for everything irrespective.

View 9 Replies
View Related
Feb 4, 2014

The first two columns are an example of my data set; I would like a formula for the 3rd column (Length+1) which will give me the length at the next age (i.e the value in the cell below in most cases) unless the following age is 1 in which case I want it to return zero.

Age Length Length+1

1 22 51

2 51 78

3 78 94

4 94 0

1 31 46

2 46 69

3 69 85

4 85 96

5 96 103

6 103 0

1 19 36

2 36 52

3 52 0

View 3 Replies
View Related
May 31, 2007

I want to return the proper value from sheet 2 into column C in sheet one. I have the current formula set up as an array but I only get the value that matches the first row in Sheet 2.

HTML Column A Column B Column C

Date Number Who

3/23/2007 902-555-2596=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-678-0352=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)

~~~~~~~~~~~ Sheet 2 ~~~~~~~~~~~~~~~~~

Column A Column B

902-555-7958Big Bird

902-555-5100Daffy Duck

902-678-0352Elmo

902-555-5752Donald Duck

902-555-7420Bert

980-123-6621Ernie

902-555-2596Grover

View 12 Replies
View Related
Oct 3, 2008

Ok, so I have a column of data in 24hr time format over a 24hr period from noon thru midnight and back to noon again. It has been necessary to make some of the cells in the column blank, and I am having difficulty extracting the first and last non-blank entries of the column (basically I want the earliest and latest time in that specific 24hr period). The time data is in order, so for example, some of the first values in the column are 14:30, 21:10, and 22:40, while some of the last values in the column are 02:20, 05:50, and 11:00, therefore the values I require are 14:30 and 11:00.

Any help that anyone can give on this matter would be greatly appreciated, but until then I guess I will keep plodding through the help sites and playing with formula until I spontaneously combust.

View 6 Replies
View Related
May 7, 2012

I have an application that on the "Main" sheet, is to extract two numbers then search for them on my "Listpoint" sheet and finally return the text to the right of the search data (e.g. K3)

Working left to right - the user pastes upto 12 lines of code into C3-C22. Formula in E3 extracts object Nos. Formula in K3 substitutes "first" number if it is a zero (with number from A3).

Left to do - Uses data (H3) to search Listpoint sheet colums C and B for a match. then returns text from Column S.

Note Listpoint has 1000 rows to search.

Main

ABCDEFGHIJKL1 2Osn Number Prog Point Text Extracted

ObJNos Zero replaced Name of Object 1 38 10 IF POINT 0|199 ON OR POINT 8|191

ON THEN RETURN FALSE 0 1998 191 8 1998 191

Example found Text 4 20 IF POINT 0|106 OFF THEN RETURN FALSE "HOLD OFF"

[Code] ............

View 9 Replies
View Related
Feb 10, 2014

I have row of dates and underneath those dates I have a row of numbers. The numbers count down. For example:

[Code] .....

At the end of the row I want a cell to return the date that the row first started going negative at. In this example, it should display 10/31/2014 (for when it went to -60).

View 5 Replies
View Related
Apr 26, 2014

I have 2 columns:

VAL0085597220EXP0000207731

VAL0122473124EXP0000207864

VAL0169412496EXP0000207879

VAL0173317027EXP0000208330

VAL0172476750EXP0000208703

VAL0173317028EXP0000209148

VAL0047743340EXP0000209666

VAL0176241996EXP0000209668

VAL0169594337EXP0014327602

VAL0047743872EXP0014327702

VAL0158185837EXP0014329002

I am trying to match where these are located in another column

View 1 Replies
View Related
Jan 28, 2008

I'd basically like a formula that finds a number (eg 220) within either the first 10 rows (or with row 5 if this isn't possible) and returns the column Letter as the result. The number will only appear once and only in one column. If it matters the columns go all the way to EC.

View 9 Replies
View Related
Apr 16, 2008

I want to produce a spreadsheet which automatically tidies up a transferral of data from a programme called sage in to a customer service spreadsheet for my company.

I am a bit of a novice but a keen one so please be patient! I do know what I want to achieve in the long run!

The problem I have at the moment is recognising products in one column and getting it to return the value in or out of stock in another. So far I have got it to recognise one product and return the correct statement (using the "IF" function) but i need it to do it for the whole column and for a range of products.

View 13 Replies
View Related
Feb 19, 2013

I'm trying to find a way to test is the max value in a range, columns A-F, comes after the min.

Example: Columns A-F have the values 4,1,3,5,2 respectively. I'd like a test to see if the min, in this case Column B, comes before the max, column D, and have an output that says yes or no. So in this example yes but if the numbers were instead 4,5,1,2,3 it would return a no.

View 3 Replies
View Related
May 30, 2014

Excel user that has been manually computing a 6000 row spreadsheet. Here is sample data and what I need. Column A goes through the alphabet (A,B, C, etc.) and Column B is numbers from 1-65. I need Column C to count the number of rows for each alphabet/number pair. K, 28 has 3 rows; K, 33 has 1 row; L, 21 has 4 rows; etc.

Column A

Column B

Column C

K

28

3

K

28

3

K

28

3

[code]....

View 2 Replies
View Related
Oct 6, 2009

My Data

Service 1 | 100 | 1.5$

Service 1 | 200| 2.5$

Service 1 | 300| 3.5$

Service 2 | 100| 11.5$

Service 2 | 125| 21.5$

Service 2 | 300| 30$

I need to extract the value in the third column according to an exact match of the first column (the service name) and a range match of the third column.

I tried SumProduct but the problem with my formula was:

Let's assum I ask for

Service 1, 200

The formula summed up 1.5 and 3.5 and returned 5

When I wanted to return one value from this table.

View 9 Replies
View Related
Jul 17, 2006

I need to return the column header in a table which corresponds to the column that the value I'm querying is in. I've been trying to use the INDEX function coupled with MATCH and COLUMNS function but I'm not getting anywhere. I've found on the web a solution that uses SUMPRODUCT but I can't get that to work either. Unfortunately, I can't supply the data I'm working with as it is confidential. I know my question would be easier to answer if I could supply it.

View 2 Replies
View Related
Sep 28, 2006

I have two worksheets. On one worksheet will be a place to enter a five digit number. In the adjacent cell, I want to search on the second worksheet in a three-column area for that same five digit number. The five-digit numbers on the second worksheet are located in the third column and the text I want returned from the second column. I cannot change the order of the columns as the first worksheet will be an "add-on" worksheet to a widely used spreadsheet within our company (this spreadsheet already has the second worksheet formatted).

View 9 Replies
View Related
Jun 24, 2012

Basically; there are three main columns in the first worksheet (lets call it "Main Data"): OrderNumber, TaskName, SignOffDate with data listed as follows. The actual spreadsheet has hundreds of order numbers but i'll keep it simple and lets go with two.

OrderNumber

TaskName

SignOffDate

1

OED

01/05/2012

[Code] ........

What I am trying to do; is sort this data in a second worksheet (lets lable it "Output") so that the sign off dates for each task; for each order; are listed within 1 single row. Ie:

OrderNumber

OED

CTN

FAD6

RFS

1

01/05/2012

17/05/2012

22/05/2012

02/06/2012

2

03/05/2012

19/05/2012

27/05/2012

02/06/2012

There are many orders in the main data; and I'm not sure what to do exactly to return the sign off dates for each task for each order without creating separate worksheets for each task name; then using vlookups to find each date.

An order may have a sign off for all task names, or none at all. In addition to this; they may not always be in the same order as listed above.

I'm using Excel 2010.

View 5 Replies
View Related
Apr 4, 2014

I am using Excel 2003. I have attached a data file here. getting the values in Q3, R3 and S3.

Scenario:

Q1 has the number = 1. So I want the cell Q3 to return 2/11/2013 as that is the cell corresponding to the Item1 (value specified in P3) with the value 1(value specified in Q1) in the cell. Basically, I need the date corresponding to cell which has the value of Q1 for the value of P3.

Similarly, R2 must have the value 2/12/2013 and S3 must have the value 2/14/2013 returned.

View 10 Replies
View Related
Apr 8, 2014

I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.

So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.

View 3 Replies
View Related
Mar 29, 2008

i have this formula in cell e28 =IF(B28<>"",LOOKUP(B28,STOCK!A1:A500,STOCK!B1:B500),"") and when its empty it returns a blank cell

but in cell e29 i have exactly the same formula =IF(B29<>"",LOOKUP(B29,STOCK!A1:A500,STOCK!B1:B500),"") but i returns #n/a

why is this and how do i stop it i want the cell to be empty unless there is a code entered in b29 in which case it will look up the relevant info for me

View 9 Replies
View Related
Feb 5, 2009

I am looking to return the larger value of 2 cells into a seperate cell.

View 2 Replies
View Related
Jul 30, 2009

I had this problem a few days ago and NVBC suggested Text to Columns, which worked initally but didn't seem to 'save' or stick... also, I'm not sure what is causing the problem to begin with. It was my understanding that Excel couldn't return a truely 'blank' value. If a cell was blank or empty, Excel still 'sees' it as Zero. WHY then, for the Love of Gummi Bears, will Excel not calculate my formula. It's a simple formula!

=A13+B13

I've tried

=SUM(A13+B13)

I've tried

=IF(ISERROR(A13+B13),"",A13+B13)

But if don't TYPE in a 0 or a number, then it just does nothing. 50 + 0 = should be 50 not blank or #VALUE!. 50 + Nothing = should be 50 not blank or #VALUE!

Attached is a small copy of what I'm trying to do. I've double checked that all number cells are formatted to numbers, not text. I tried the Data:Text to Columns trick. I've tried Tools:Options:Show Zeros. I've tried Manual and Automatic Calculation. They shouldn't have to type in 60 cells of Zero to get the simple calculation.

View 2 Replies
View Related
Jan 7, 2013

I'm linking one sheet in a workbook to another. The formula is simple,

='Link 1'!E1

If there is data in the cell, it replicates it which is what I'm after. However if there is no data in the cell it returns a "0", not what I want at all.

View 2 Replies
View Related
Nov 11, 2013

I am trying to return a cell with a simple yes or no answer and having a little difficulty.

I have entered the following formula which is returning #NAME

=IF(E4<=1.25,ORH4<=0.01,yes)

Calculation needs to be as follows:

Install Cost <1.25 and Profit atleast 0.01

The YES NO needs to be in the Invite? column

View 4 Replies
View Related