# Excel 2003 :: Returning Multiple Values From Index?

Apr 20, 2013

I am using the following formula:

=IFERROR(INDEX(drange,SMALL(IF(AND(qrange="SH",trange>30),ROW()-6),ROW(A1))),"")

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

## Index Across Multiple Sheets Returning Multiple Values

Mar 21, 2014

Index across multiple sheets returning multiple values

I am building a payroll workbook and I need to build a sheet that will allow me to choose an employee name and the formula will return all the data related to this employee. I found this forumla to do this: =IF(ROWS(...)<=...;INDEX(INDIRECT(...);SMALL(IF(...=...;ROW(...)-ROW(...)+1);ROWS(...)));"")

But in my workbook, the data is on multiple sheets . How do I adapt this formula to look into 12 sheets?

that data would be returned in order of the date of the pay.

file: Test formula on payroll_v3.xlsx

## 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.

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.

## Excel 2003 :: Formula For Counting Values Across A Range Using Multiple Criteria Across Multiple Sheets

Feb 9, 2014

I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.

I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.

Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.

I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.

## Excel 2003 :: How To Add Multiple Values If 2 Cells Match

Dec 2, 2011

I have a sheet that column G and column H has duplicate values. I would like to be able to put a formula in a cell P4 that will search thru column G and column H and if there are any matching items then add the corrisponding number in column I.

So say cell G8, G25, and G30 have the same and H8 and H30 have the same value, i would like this to add the values of cell I8 and I30.

Windows XP
Excel 2003

## Excel 2007 :: Lookup One Value And Returning Multiple Values (Horizontally) With Two Sheets

Jul 31, 2014

I'm basically working from 3 sheets for this so I'll start with an example of the data I'm using:

1st sheet:

table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
margin: 3px;

[Code]....

I have 180 rows of data like this one in the first sheet

Second sheet(named sheet 1) is not used for this

Third sheet(named sheet 2):

table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
margin: 3px;

[Code]....

Basically I'm trying to find column #3 value in my third sheet and return the second column value. Problem is that the data is located more than once in the third sheet so I need the value of each one of them. So, with this example, Q5942X is located twice in the third sheet and each time, it has a quantity of 2. I would need either to return the value 2 twice horizontally or even better, add the two together. The first sheet, the example is row #45.

This formula should be in the column following "majoration".

I am using excel 2007 and windows 7.

## 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....

## INDEX With Multiple Criteria And Returning Multiple Rows But Only The Two Most Recent

Jun 10, 2013

I have a spreadsheet of prices (raw data), and I need to pull out both the most recent price for each item for a specific customer code for each item, as well as the second most recent price, (essentially the old price), for that customer code for each item.

I have columns for ITEM (code), PTC (Customer Code), PRICE, and EFF (Effective Date). For each item code, there are multiple prices for multiple customers, such as in the linked workbook.

I am currently using the formula, =INDEX(PRICE,MATCH(1,(F2=ITEM)*(G2=PTC),0)) to pull out the last price. This works because the last price is on top based on sorting. What I need is a formula that pulls out the next oldest price for that item/ptc combo. I tried using the SMALL function to no success.

[URL]

## Index And Match Function Returning Same Values

Dec 15, 2009

In cell G51 of sheet "My Overview", if two people have the same scores it pulls through one person twice in F51 and F52, how can i overcome this?

## Returning Second / Third Etc Values Using VLOOKUP / INDEX / MATCH

Mar 1, 2014

making a forumla that will return the second, third ewtc values using VLOOKUP or INDEX/MATCH. Attached is a dummy file almost identical to the one I'm working in. In the first sheet there's a dependent drop-down list with the values Sub1, Sub2 and Sub3. This corresponds with data in sheet two listed next to these names. The problem is when I try to populate the table below using VLOOKUP etc it just repeats the first row over and over rather than defaulting to the second and third row results

## Index With Multiple Match Formulas Returning Undesired Value?

Nov 20, 2012

I am building a tool for work, and in one of the sections of the main worksheet, I would like the cells to pull specific data from another worksheet that contains the results of a web query. The web query pulls a table of data, with a double header (rows 3 and 4). One column header is for the date and the other is for the time the data below corresponds to. There are also row titles along the left hand side.

I need a formula that will find the column that meets two conditions:

1) Today's date
2) A specific time (referenced from a cell on the main sheet)

It must then pull the data from the cell in that column that is in the row marked "Total".

Here is an example of the data set from the web query:

UAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBT3
Total19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-
Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-
Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-
Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-
Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-

[Code]......

I have done quite a bit of looking on the web and tried out several different formulas/variations. I think I'm getting close, but just can't get it quite right. Here are a couple that seem close and their results:

=INDEX('Rodeo Data'!\$D\$5:\$JS\$50,MATCH("Total",'Rodeo Data'!\$B\$5:\$B\$50,0),MATCH(J3,'Rodeo Data'!\$D\$4:\$JS\$4,0)*AND(MATCH(TODAY(),'Rodeo Data'!\$D\$3:\$JS\$3,1)))

This one correctly finds the date and "Total" row, but seems to ignore the MATCH(J3,'Rodeo Data'!\$D\$4:\$JS\$4,0) section, which is designed to find the specific time in row 4. It returns either the "Total" row value from the first or last column with the correct date depending on which match type argument I use.

=INDEX('Rodeo Data'!\$D\$5:\$JS\$50,MATCH("Total",'Rodeo Data'!\$B\$5:\$B\$50,0),MATCH(TODAY()&O3,'Rodeo Data'!\$D\$3:\$JS\$3&'Rodeo Data'!\$D\$4:\$JS\$4,))

This one returns the standard "#N/A" error message.

I am using Windows 7 and Excel 2010. "Rodeo Data" is the name of the sheet that contains the web query data I have included. The J3 and O3 references are cells in the main worksheet that contain the time I want the match function to find.

The desired output from the formula would be if I referenced it to today (20 Nov 2012) and 19:45, it would return the value of 65,826.

It may also be worth noting that the number of columns and rows in the data set is dynamic.

## Excel 2003 :: Index And Match - Table With Header Row Of Scores

Oct 4, 2013

Excel 2003

I have a table with a header row of scores 1-10 in B4:K4.

In column A I have a list of locations A5:A68. People in each location were asked to score an event between 1 and 10. The count of their scores is under B4:K4, eg Location 1, 3 people scored 1 (entered in B5), none scored 2 (C5), 6 scored 3 (D5) etc through to the score for 10 in K5.

What I am after is the average for each location so that in L5 I can say 'for all the respondents for Location 1, the average was:...

This may be a bit more complicated than it appears as presumably there will be a requirement to multiply the number of respondents by their scores and then ... ?

## Excel 2003 :: Can Index Through Pivot Table Page Field List?

Nov 1, 2011

I've created a pivot table and I'd like to index through each "value" in the page field and then copy the results to another sheet, one sheet per field returned.

I can't figure out if it's possible to index through the list though. Is it possible?

## Excel 2003 :: Double Lookup (Index / Match Or VLookup) Within Same Column?

Mar 15, 2013

I have two spreadsheets, one with master file with original data and one that needs to pull in the original data. My issue is all the data to match off of is in the same column, and a number of other files link to the master file, plus it is used externally, so I cannot alter it, and I would rather not create a mock/copy file. Is there a formula that can look for 2 different items within the same column? Would prefer not to use VBA, but if that's the only option I'll take it. I am using Excel 2003.

Here is an example of the setup - I would need the formula to reference off the two different items/categories in the column, so lookup off the 'St. Louis' and following that, lookup off the produce items.

## Returning Multiple Values With One Lookup Value

Aug 16, 2014

How the following problem gets sorted out

ColumnAColumnB ColumnEColumnF
33rohan55
44tata55
55dev44
55rony33

As you can see from above i want to lookup all the values in column E in column A and return the corresponding value in column F.

## VLOOKUP And Returning Multiple Values

Dec 15, 2013

Trying to pull data from one workbook to another using Vlookup.

The worksheet has 7 columns (date, day of week, time or arrival, package, departure time, patron count and group name) I would like to pull all the data for a specific date in a seperate workbook.

I know I require a nested formula but cannot seem to get the variables correct:

## Returning Values With Multiple Criteria?

Apr 11, 2012

I'm trying to get a value to return based upon three criteria, sales rep, compensation plan and calculation method.

For each sales rep (there are 20 reps), there is an option for one of five comp plans and for every comp plan, there are six calculation methods (these six methods are the same for every one of the five comp plans).

I know I need to use the Indes(Match()) set-up, but I'm having the darndest time figuring out how to set up the source matrix to do this right.

## Use INDEX To Lookup Multiple Values In Multiple List

Dec 8, 2013

I am using the below array formula in G2 (that I then drag across) to show the score for all the times "mike" appears. I would like to match all the times "mike" OR "red" appears, so that the value in K2 is "99".

=INDEX(\$A\$2:\$C\$9999,SMALL(IF(\$A\$2:\$A\$9999=\$E2,ROW(\$A\$2:\$A\$9999)-1,"hh"),COLUMNS(\$G2:G2)),2)

A
B
C
D
E
F
G
H
I
J
K
L

1
name
score
color

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

## Vlookup Returning Multiple Values In Different Columns

Apr 18, 2014

I have attached a file with an example of what i am looking for. The data in columns A and B are my original Data. There are multiple instances of e-mail address on the left but I need to bring in each data horizontally. I would like a formula to enter into E2:J6 to pull the information in.

Book2.xlsx

## Returning Multiple Values As Dropdown List?

Apr 24, 2013

Actually I plan to create an eRequisition Form for our branch sites. My focus would be the consumable items of Lexmark printers. I want to create cascading drop-down lists based on data table (worksheet : All) as per attached. From lots of examples I've seen in this or other similar forums, I notice that in order to allow this cascading to work is by creating column with header name identical with the input in the 1st List. But I'm thinking, if I need to do that, then there gonna be hundreds or maybe thousands columns to create as the number of branches are expanding, so I think tht should not be practical.

I found examples that shows how lookup 1 value can return multiple values but I totally cannot think of a way to allow those multiple values to be returned as dropdown list rather than displaying all in rows of the worksheet.

What I need is that, in 1st list, upon selecting the branch, it'll populate all items related to the branch and when selecting the item, it'll populate brand/model list for the particular item only. The input in the dropdown list should not have duplicates. Been trying using pivot to do this with macro that enables auto refresh once any changes made in the table.

create this cascading drop-down lists just based on the table as per attached? I really want to avoid having to create extra tables to allow this cascading to work.

[URL]

## Wildcard Searching For Multiple Values And Returning A Result?

May 27, 2014

I have been trying to find a formula for this but I have yet to find one that works for me.

Data;

English - United Kingdom, Czech, Spanish
UK - United Kingdom
Swedish
English - US
French
US
France
German
Portuguese
+8000 more various values

I am looking for a formula that can search for multiple values e.g. english, us, uk and united and then return the value "Yes".

I was looking for something that includes wildcard searches or contains so that it is not affected by upper or lower case but combining this with multiple searches was proving difficult.

## Returning Multiple Values With Duplicate Cell Reference

Jul 11, 2014

From the following example:

A1 TAG B1 mileage
G10-1622 15246
G10-1622 15246
G10-1583 15246
G10-1622 28659
G10-1195 28659
G10-1622 28659

desired results

Enter tag in cell
G10-1662
Mileage
15246
15246
28659
28659

I would like to enter a particular TAG # in I5 and return all the mutiple associated data

See attached : tire2.0.xls‎

## Lookup Type Function Returning Multiple Values

May 23, 2007

I have attached a sample spreadsheet which describes my problem.

In C2 and C3 under the first business coalitions column, I have written in text what I would like to be displayed automatically with a formula. The trouble is that, as you can see, Abbott Laboratories is a member of 3 business coalitions. Therefore, a simple vlookup doesn't work because that would only return 1 result.

## 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

## 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.

## Excel 2007 :: Returning Values Based On Criteria?

May 17, 2014

I'm using Excel 2007

I have the below data in sheet 1

In a separate column on sheet 2 i want to return the value of the data in ACC1 if the data in DEB/CRED is LC or SC and the value of the data in ACC2 if the data in DEB/CRED is SD

DEB/CRED
ACC1
ACC2

[code]....

## Return Multiple Values Using Index?

Jan 15, 2014

I have a table with a couple thousand rows. I am having trouble writing an index and match to return multiple matches.

I want to use the value in Column 1 (Search Name) to search through Column 2 (Code) and when it finds a match, return the matches from the Return Item column.

Not all of them will have a match (A1AT), but some of them will have multiple matches (ALB2).

Would the output be horizontal across? Where the various matches would come under columns 4, 5, 6, etc.

SearchName
Code
Return Item
A1AT
SDES
TDRNC

[Code]....

## Excel 2010 :: Searching For Files By Cell Value And Returning Values

Jun 24, 2014

I need a formula (but most likely a VBA macro) that will search through a folder for a file than get data from that file. The files are named in systematic way, but I need the entire formula to work from inputting a mold number in one cell. E.g. I input 6291 in cell A2 the vba macro searches for file “6291 mold.xlsx” and returns a range of numbers as well as pictures in specified cells. Is this possible? If so how?

The closest thing I have found is VBA macro that retrieves a list of media files in a folder, I listed the code below.

[Code] ....

[URL] ....

## Returning Match And Adding Values On Multiple Worksheets To A Master Sheet?

Aug 4, 2012

how I can keep a running total of information added to a different worksheetS. They would not always be the same peson ID so I would like to return a match on any unique ID number found on multiple worksheets and add their values on a master front sheet. i.e this becomes a running total of items purchased per ID number.

## Excel Vlookup Between Two Dates Returning Multiple Data?

Jul 11, 2014

Basicly i have a list of information and i need to be able to enter a start data in one cell and a end date in a different cell and then return all the dates between them.