Excel 2003 :: Returning The Date In Column For Matching Values

Apr 4, 2014

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


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


Matching Two Values In Same Column And Returning Value From One Row?

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

Excel 2003 :: Returning Multiple Values From Index?

Apr 20, 2013

I am using the following formula:


to return the name (drange) of a person who was visited by "SH" (qrange), more than 30 days ago (trange).

There are faults in my formula, and if I'm honest I don't entirely understand it !

I need to return all of the names of people who were visited by "SH" more than 30 days ago. So I need the next value, and the next which is also over 30, by copying the formula down to the next cell and the next. My problem is that I get the first value (which is correct) and then the next ones are blank.

To make matters worse, the first value I get is only correct if i DON'T enter it as an array. If I do enter it as an array, I get the first row of the spreadsheet.

Using Excel 2003

View 9 Replies View Related

2003, Returning 2 Largest Values

Aug 27, 2008

I am looking to create a formula to add together the two highest values of a list of 10 numbers.
I know you can use something like large(a1,a10,1)+large(a1,a10,2) if numbers are in an array but my problem is numbers arent
they are in random positions along a row....

View 12 Replies View Related

Excel 2003 :: Adding Values If Column Next To It Is In Certain Range

Sep 9, 2013

I have this code working fine in excel 2007 but I need to get it working in excel 2003.


I have attached a test document to show what I'd like to do. It is basically adding values if the column next to it is in a certain range.

counting columns in Excel 2003.xls

View 3 Replies View Related

Excel 2003 :: Finding 3rd / 2nd And Highest Values In A Column?

Mar 7, 2013

I have a spreadsheet and on sheet 1 it currently has 45791 rows filled with data and it increases each day.

Each row looks like this


On Sheet 2 I have 3 columns. These are the list of horses that are running on a particular day.


What I want to do is to be able to list in columns D,E,and F on sheet 2 the last three ratings the horses achieved from sheet 1.

how to acquire the latest rating of the horse by using the following formula.


This works a treat but for the life of me I can't fathom out how to get the formula to pick the latest three ratings and place them on sheet 2.

I am using excel 2003

View 9 Replies View Related

Matching Date In Another Sheet And Returning Cell Value

Mar 29, 2012

I have in my 'Daily' sheet I9 which needs a formula to look at the date in C9 and the scan range 'Weeks' F6:AN41 and find the cell with the matching date and then whatever text is in Row 2 of the column the date was in will be returned.

View 2 Replies View Related

Returning Matching Value From An Array Based On A MAX Date.

Feb 25, 2010

I have an Excel spreadsheet (XP - 2007) listing Job Nos. in the first column, with several columns of Station assignments and dates.
Both planned dates and actual dates are included, adjacent to each other. The dates are not necessarily in a straight ascending or descending order. Separate arrays exist for: Plan Nametags, Actual Nametags, Plan Dates, Actual Dates.


Job No.Sta1 PlanSta1 ActSta2 PlanSta2 ActSta3 PlanSta3 Act1A10001-Feb-101-Feb-106-Feb-106-Feb-101-Mar-101A100116-Feb-1016-Feb-1019-Feb-1022-Feb-105-Mar-101A100225-Feb-1025-Feb-102-Mar-104-Mar-1010-Mar-10@
I need to capture two pieces of information for each Job No. on a daily basis:
1- The 'Planned' Station for the build, based on a match of the 'Plan' date fields to a pre-populated 'Report Date'.
I've been able to do this (using INDEX-MATCH function).
2- The 'Actual' Station location for the build, based on the Maximum 'Actual' date entry in the row for each build.
(In the example above, Job# 1A1001 would have an 'Actual' location of 'Sta2 Act'...)
I need to figure out how to accomplish step (2) above. I've made several attempts with INDEX-MATCH and LOOKUP functions, without success.

View 10 Replies View Related

Excel 2003 :: Cell And Column To Highlight When Date Arrived

Aug 20, 2013

I am looking for a formula for a date.

I want the cell and column to highlight when the date has arrived, and I want it to remain highlighted for 7 days.

I have got as far as =F1=TODAY()

View 2 Replies View Related

Formula For Matching Dates And Returning Values

Jun 18, 2008

I have a worksheet with a data sheet that has the values for a number of portfolios over a time period (one month)then I have a corresponding sheet for each portfolio (in the attached example I have included only portfolio 1). What I am trying to do is to make a formula in the sheet Portfolio 1 that matches the date in the Portfolio 1 sheet with the data sheet and by matching the date returning the value for the Portfolio 1 for the date ie date 2008-06-01 should return for portfolio 1 (from the data sheet) 500.

I have tried with varying Index and matching formulas and I do believe that this is the way to go.

View 3 Replies View Related

Matching 2 Column Entries Returning Third?

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.


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

Matching Two Columns Returning Location In A Column

Apr 26, 2014

I have 2 columns:

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

View 1 Replies View Related

Excel 2003 :: Matching Row Data

Nov 28, 2011

I have five columns of data: A & C are time measures; B & D are values associated with each time measure; and E is a matching value column that I want to populate. I am trying to match times and place in col E the Val 2 that matches times 1 and 2. Though many matches lie on the same row, there are many that don't (the real data uses several thousand rows). For example, Val 1 in cell A5 (100) is associated with cell C7 to find a matching value of 4.5 in cell E5. Note, occasionally, there will be replicate values for Val 2 (like cells C9 & C10). I need to match the one further down the list.

Excel 2003ABCDE1Time 1Val 1Time 2Val 2Match203.902.72.73484.7482.12.14545492.33.351006543.34.562003554.472192.71004.57.782015.592196.6102197.7Sheet1

View 3 Replies View Related

Excel 2010 :: INDEX MATCH Only Returns First Value Instead Of All Matching Values In Selected Column

Jul 18, 2014

Excel 2010: I'm working with a sheet where I would like to get accurate results on the make and model of cars sold in a given week. The workbook contains several worksheets each representing weekly sales numbers for different vehicles.

The Make-Models tab has the lists used to create the drop-down selections.

The Sales tab allows one to create an ad hoc report on the total number of vehicles sold, by Make and Model. Cell B2 is named 'SelectedModel' and used in the formula found in column E, Total Sold.

Problem: When I select Honda, for example in B2 of the Sales worksheet then select Accord in cell B3 of the same. The results returned in E2, E3 and E4 are 2, 3 and 4, respectively, instead of 8, 3 and 7.

2 Honda Accords were sold on Monday and 6 on Tuesday of the same week. My formula is only returning the first matched value of 2 whereas I would like it to return 2+6, 8 for week 1. Same goes for other weeks.

Select any other make and model and notice the problem follows. I believe the error is due to incorrect usage of the formula or incorrect formula altogether.


View 9 Replies View Related

Matching Two Values To Two Other Values And Returning 3rd Value

Aug 15, 2008

I have a list of eastings and northings for specific incidents, then in a seperate sheet I have a list of eastings and northings for postcodes.

What I want to end up with, is which postcode the incidents are in!

So it means getting Excel to find the closest match of two values to two other values and return a corresponding value.

Here's what it looks like!

Incident Easting Northing

01 437302 113607
02 437340 113673
03 437366 113701
04 437366 113701
05 437474 113581

Postcode Easting Northing

SP410TQ 430449 91321
SP410PY 429158 91339
SP410QB 429258 91375
SP410PZ 429288 91407
SP410EA 428940 91444

I want to end up with

Incident Postcode
01 SP7185H
etc etc.

View 9 Replies View Related

Matching Two Values To Two Other Values And Returning A 3rd Value

Aug 15, 2008

I have a list of eastings and northings for specific incidents, then in a seperate sheet I have a list of eastings and northings for postcodes.

What I want to end up with, is which postcode the incidents are in!

So it means getting Excel to find the closest match of two values to two other values and return a corresponding value.

Here's what it looks like!

Incident Easting Northing
01 437302 113607
02 437340 113673
03 437366 113701
04 437366 113701
05 437474 113581

Postcode Easting Northing
SP410TQ 430449 91321
SP410PY 429158 91339
SP410QB 429258 91375
SP410PZ 429288 91407
SP410EA 428940 91444

I want to end up with

Incident Postcode
01 SP7185H

etc etc.

View 9 Replies View Related

Align Matching Row Values Column And Remove Non-matching Row Values?

Jan 27, 2014

see attachment below for easier comprehension of what I need to be done. Note that I have around 20 subjects (2 in example) and about 15000 values per subject (5 per subject shown) Basically, I want matching rows to be aligned (see grey) and non-matching rows to be deleted, throughout the 20 or so subjects.


View 6 Replies View Related

Excel 2003 :: Reposition Data And Sum Quantity Based On Matching Reference

Feb 15, 2013

I'm using excel 2003 and using VBA I want to convert data from my original data below.

The first row is just to identify the columns below it.

71723,121222010,EXAPLECOMPANY,2-13-2013,1.0000,MPG TBS01 TB,65.9900
71723,121222010,EXAPLECOMPANY,2-13-2013,1.0000,MPG TBS01 CH,86.5800
71721,121221142,EXAPLECOMPANY,2-13-2013,1.0000,MPG PB01,75.9900

To be converted to the following:


"MPG TBS01 TB","1.0000","65.9900","65.99",
"MPG TBS01 CH","1.0000","86.5800","86.58",

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

The converted output data above can be within the same document or output to a text file.

View 1 Replies View Related

Excel 2003 :: Date Formula For If Given Date Is Before Or After Today Or If Nothing Entered

Jan 17, 2013

Cell B1 contains a date, then B2 contains a formula that says:


A1 contains a green tick and A2 contains a red cross.

What I am trying to add is that if B1 contains no date then B2 needs to be blank.

I tried using =IF((A1="",0),(A1>TODAY(),A1,A2) to get it to show a 0 if there was no data but this doesn't work.

I am using Excel 2003.

View 4 Replies View Related

Align Matching Values In Columns A And B And Bring Corresponding Values With Column B

May 15, 2014

I would like to align the matching values in columns A and B but I want the values that correspond to b in c,d and e to go with the column b value.

C D and E don't necessarily have a value in there but if it does it needs to move with B

View 5 Replies View Related

Excel 2010 :: For Selected Date Between Dates Sum Values In Another Column

Jan 26, 2013

We have arrival dates and departure dates for guests and would like to figure out the number of meals needed on a particular date.

Named ranges: NumGuests _ArrivalDate _DepartureDate _CheckInTime _CheckOutTime
Need number of breakfasts
Number of Lunches
Number of Dinners for a given day of the week.

So if a guest is here say Jan 25 arriving after 9am, staying 2 nights they will have Lunch and Dinner on the 25th, BLD on the 26th and BL on the 27th. Since by default most arrive after 9 (miss breakfast), we can eliminate the checkin time for the breakfast calc and make the first one for the next day.

There might be other guests staying through that time period. The ******* would like to know how many meals they will need to prepare for the week.

Windows XP, Excel 2010

View 5 Replies View Related

Formula To Total Values In Column Matching Text In Adjacent Column

Dec 20, 2013

Formula(s) to do as explained in the attached example.


View 9 Replies View Related

Returning Value From Column Based On The Values From 2 Others?

Aug 7, 2014

I have a table of data-
IDDescriptionDatePax Count

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

(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

Comparing Values And Returning Different Column

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-555-5752Donald Duck

View 12 Replies View Related

Matching Date In Excel

Aug 7, 2012

I have this nagging problem with my current data. So let i have two "sites" which contain a date column. Some of the dates matches. So i want to match this date in excel and not manually go cell by cell checking for a match. how can i do this with excel?

a better illustration of my question. So basically i want to match concord 4/1/1999 with the other concord of the same date.

Site name
Site name


[Code] ........

View 4 Replies View Related

Returning Values Based On Location In Column?

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

Vlookup Values In 2 Columns Returning Value From The Third Column

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

Excel 2003 :: SUM According To Date And Time?

Jun 11, 2013

I have to analyze our abandoned calls for the year. So far there are close to 2000 abandoned calls and I need analyze them according to the month and the time they were received.

I have two worksheets in my workbook the first worksheet is named DROPPED, this is were all the data is. The second worksheet is called DROPPED CALLS SUMMARY, this is where all the analysis happens.

On the DROPPED CALLS SUMMARY Worksheet I need to have the Sum of Column I IF ColumnA is equal to Jan-13(or whatever month) and Column B is equal to 8:00 AM (or whatever time). I'm using Excel 2003 so I cannot use the SUMIFS Formula and I can't find a formula to work with.



View 5 Replies View Related

Excel 2010 :: Using VBA For Sub-Totaling Returning Unexpected Values

May 8, 2014

This relates to this thread - [URL] .....

This is the only macro in this file

[Code] .....

It will perform the sub-totalling for the column that has the current active cell

When I select Column I

It does.....
-for each blue cell it finds it provides a total of all the white cells bellow it
-for each yellow cell it find it provides a total of all the blue cells bellow it until it reaches a yellow cell

It works backwards, so not exactly as I've just described, but that isn't the problem

Problem is -
UK Excel 2010 - results are as expected
US Excel 2010 - returns zero values for totals

We've possibly narrowed the problem down to when it looks at cell properties, more specifically -- If Cells(rowX, 1).Interior.ColorIndex = 20 Then

How to get results in blue and yellow cells when you select a cell in column K then execute the CreateTotals macro.

Attached File : Example-1p.xlsm

View 6 Replies View Related

Returning Values From One Excel Sheet To Another Using Cell Addresses?

Aug 16, 2014

I have two sheets: sheet1 and sheet2.

In sheet2 I have a column "C" called addresses and in that column I have actual cell addresses such as $J$740, $H$756, etc., all referring to cell locations in Sheet1.

In sheet1 in column "B" are names.

I would like to be able to return the names from sheet1 column "B" to column "G" of sheet2 that belong to the cell address from column "C" in sheet2.

Is there an Excel formula or vb script that will do this?

I tried "=INDIRECT("sheet1!"&C2)" but all that did was return was is written in the cell address (for example $J$740) but what I want is the corresponding name in column B.

View 4 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved