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

View 1 Replies

Reverse VLookup (Index Match) To Return Multiple Values Based On Single Lookup Criteria

Jul 11, 2012

I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.

View 1 Replies View Related

Return Index Out Of Two Values

Apr 23, 2012

I need to return the index based on values from 2 columns with largest amounts, below the example: From the below table, the index I need returned will be "Purchasing Data Inquiry" because even though this one does not have the biggest % has more number of hits (51) based in column D. So I need a comparison of column D and E to subtract the Index.

ABCDE1ProductYesNoTotal%2SAP-Shopping Cart1141573.33%3Purchasing Data Inquiry4655190.20%4CIP Login-Security606100.00%5SPEED Item BOM101100.00%6BCRN-Business connect RosettaNet202100.00%7Access Manager-IBL Customer Entitlement51683.33%8ISM-Integrated Shipping Memo202100.00%9EAM-Enterprise Access Mgmt1511693.75%

View 3 Replies View Related

Return List Of Values Index/match

Aug 26, 2009

I have a list of data that I want to average the number in column B when there is a match in column A

COLUMN A | COLUMN B
alpha | 100
beta | 200
gamma | 300
alpha | 400
beta | 500

What I'm looking to do is return the MEAN of values in column B where column A = alpha. Though, I would settle for returning a list of the values that match the criteria. Index/Match combinations don't seem to be helping me because they only return the first match, I'd like to return all matches.

View 4 Replies View Related

Index Function To Return List Of Values

Apr 3, 2012

I am looking for a formula which will return the names of in Column A where Column B is equal to 0.

ColumnA
David ColumnB
0 Joe 0 Susan 1 Steve 2 Janet 0 Bonnie 8 Emma 8

View 4 Replies View Related

Index Function With Multiple Ifs Won't Return More Than First Result

Aug 11, 2014

I am attempting to get an INDEX function with multiple "IF" statements to return more than one result. Building multiple IF statements. That portion of the below formula works. However, I can only get the INDEX formula to return the first, single result.

Formula:
=IFERROR(INDEX(Func_Area,SMALL(IF(Const_Start<=DATE(YEAR($B$2),MONTH($B$2),DAY($B$2)), IF(Cost_End>=DATE(YEAR($C$2),MONTH($C$2),DAY($C$2)),ROW(Func_Area)-MIN(ROW(Func_Area))+1,"")),ROWS($A$4:A4)))," ")

Func_Area,Const_Start,Cost_End are all Named Ranges on Sheet "Proposed". My intention is to return a list of projects on Sheet Test1 for all projects starting construction after a certain date and completing construction before a certain date. I suspect something in my ROWS fuction or ROW-MIN+1 is wrong.

This formula returns the first result in the first row, but every subsequent row is blank...see attached spreadsheet.

View 3 Replies View Related

Can Index Or Match Return Multiple Results

Nov 27, 2006

I've spent the last week trying to do the following, with little to no success....and I'm running out of hair to pull out!!

I have a worksheet (let's call it DATA) with the following column headers:
* Site Name
* Year Initiated
* Status

plus a whole bunch of other columns of information.

On a separate worksheet (let's call it RESULTS) I'm trying to create a summary of all the Site Names where the Year Initiated = "2004" and the Status = "Active". There will be multiple occurences where this is true.

I then want to create further results where the year = 2005, 2006, etc., etc. I've been trying to use the INDEX function with variations of MATCH, SMALL, ROW, etc., etc., but so far, no luck.

Advanced Filtering is not an option as I need to do further manipulating of the results on the RESULTS worksheet.

View 12 Replies View Related

Offset / Index / Match - Using 3 Functions To Search For And Return Values From Data Sheet

Jan 19, 2014

Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.

The attached spread sheet has performance data for a group of employees.

What I need to do is find a particular employee then return a value for one of the category's.

For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.

Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.

Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.

Maybe Offset-Index-Match is not even the way to go here?

View 7 Replies View Related

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

View 4 Replies View Related

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

View 9 Replies View Related

Match Multiple Values With Multiple Values From Another Table - Return Single Value

May 12, 2014

I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...

Attached worksheet : Test booklet.xlsx‎

View 4 Replies View Related

Macro For Multiple Lookup Values And Return Multiple Values

May 9, 2013

I have attached sample sheet.

I need to populate the Rep Names looking up 3 Criteria (Client Id,Curr Cov Id,Dom Buy Grp Id) from the Table 1, either one matches the Rep Names has to populate.

The data has to be pasted on WIP_Sheet in different columns.

View 3 Replies View Related

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

View 9 Replies View Related

Multiple Match Values - Index Dynamic Table

Aug 19, 2008

I'm trying to extract the selected suppliers orders from a 1000+ rows list ; like appearing with the example of supplier "D" ; and the new one should be dynamic , it means if any data added to the source orders list and if the supplier was one of the selected the new table automatically update ; or can be refreshed

View 9 Replies View Related

INDEX / MATCH Using Multiple Static Lookup Values From Dropdown

May 20, 2014

I am trying to do an INDEX/MATCH to return a value based on multiple lookup_values.

The workbook is attached, and what I am trying to accomplish is this:

If 50, 75, or 90 are selected from Column D drop down of this (Main) worksheet, then return Column A, B, and C values for those rows to the Report worksheet.

So in my sample spreadsheet, John, Jim, and Jack's information should be returned on the Report worksheet because their Column D shows 50, 75, and 90. Alice, Wanda, and Cindy's information should not be returned on the Report worksheet because their column D shows Budget Only, Contacting, and 100.

My current INDEX/MATCH formula is =INDEX(Main!$C2,MATCH(50,Main!$D2),0), but this only works when column D has 50 chosen because I have only asked MATCH to return info if it finds something with 50 because I do not know how to ask it to search for multiple lookup_values.

So, as you will see, only John's information is returned on the report worksheet, but Jim and Jack's are not.

View 7 Replies View Related

How To Return Multiple Values In Multiple Cells If Argument Is TRUE In IF Functions

Jan 8, 2013

I want my IF function to populate two cells with two values if argument for IF function is found TRUE. Is it possible?

IF(logical_test, [value_if_true], [value_if_false])

value_if_true = return multiple values in mutiple cells (for example put number 8 in cell A2 and number 10 in cell A3 if function is TRUE)

How would I do that? I tried putting IF(logical_test, (A2="8",A3="10"), [value_if_false]) but it is not possbile...

View 3 Replies View Related

Finding Multiple Values To Return Multiple Values

Apr 18, 2008

I have a bill of materials with a description column. I want to search that column for various words (ie. wheel, screw, spacer, shelf, etc) and return a value into another new column depending on that value (wheel inputs wheel, screw inputs hardware, spacer inputs hardware, shelf inputs shelf).

How Excel shows you how to search will only return one value because I can't use an else statement:

View 14 Replies View Related

VLOOKUP / INDEX And MATCH To Return All Values That Match

Feb 4, 2014

I have two sheets of data , sheet A and Sheet B.

Sheet B contains a column called "Name" and for each name, and for each name there are corresponding numbers. In sheet A, I have a "list of interest" in column A. What i want to do look through the data in sheetB to find a match from the list of interest and return the corresponding letters, located in column A.

I have filled out the first two rows of results that should be returned as an example.

One idea i had was to put a vlookup formula in each column result 1 to result 6 so i can catch all 6 "Serves" columns from column B, but there may be duplicates in the serve columns and vlookup only reports the first match.

View 3 Replies View Related

Index And / Or Match Formula With Multiple Match Required To Return A Value

Jan 21, 2014

See attached file, "Rate Sample Index-Match Formula".

I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".

In the 1st tab, "TEST FILE" there are a series of columns as follows;

A = Service
B = From
C = To
D = Rate

In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)

The formula needs to do the following;

1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green
2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue
3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59

For Example;

The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.

Additionally, if we were to add service as an additional lookup match how would this work?

View 3 Replies View Related

Return Multiple Values From Multiple Sheets?

Apr 23, 2013

I have a workbook with multiple sheets in it. One sheet is an Overview sheet, and the others represent the months (JAN, FEB, MAR, etc). On the Overview sheet, I have a few columns of data set up that I need to retrieve from the various sheets within the workbook. The thing is, this data can occur more than once on any particular sheet and it's very likely to occur multiple times throughout the workbook. For example, compressor #1 was blown down on January 1, January 15, February 5, February 9, February 12, April 22, and so on. Compressors 2-5 were also blown down multiple times throughout the year, as well as various other equipment that I have listed out throughout the numerous monthly tabs. On the Overview sheet, I have the columns Equipment, Date, Temp, Begin PSIG and MCF Loss. I'm needing to get the information from those tabs into these columns.

I first tried a simple VLOOKUP. After all, it's worked for so many situations similar to this, why wouldn't it work here? I quickly found that it won't work here because I need to return multiple results, not just the first one it comes across.

When I copy the formula down it seems to return the same results in every cell, not the next result, and the next, and the next, and so on.

View 9 Replies View Related

How To Return Multiple Values

Jan 21, 2014

Basically I have a workbook that contains two worksheets, sheet1 contains flight numbers in column A and column E contains the Charge code that belongs to the flight number. The problem I have is that in some cases the flight number can appear more than once in the column and the corresponding charge code is different. Is there a way of using a look up to find the flight number and bring back the corresponding Charge code in sheet2?

Please note I have attached a summorised version of the workbook, the actual Travel# in sheet2 is in column R and the Charge code in sheet2 is in column AD.

View 6 Replies View Related

Return Multiple Values With One Value

Mar 26, 2014

I have list of 2043 rows and i have like 29 values i need to look up and return me several values that associated with that particular number but I want it to list them horizontally or even vertically but whenever i get the right code in one cell and i drag that green box it formats it in a weird way that gives me an error.

Here is the sheet I would like an VBA or macro.... my VBA code is already on here but somehow i get an error.

Book3.xlsx

So what i did was the first code followed this example still get errors [URL] .....

So match pcode columnsA to find and return the columnB result somewhere else on the sheet list them out horizontally preferred or vertically.

View 1 Replies View Related

UDF To Return Multiple Values

Jul 16, 2014

I'm looking to create a UDF called ListItems which will look through a range and create a one column contiguous list of non-blank values.

e.g. below is Range("A1:C3") --> ignore the Column Headers, they're just included for visual reference.

Column A
Column B
Column C

John
Peter
Mary
Andy
Dave
Karen

If the user used =ListItems(A1:C3) in cell D1, the result would be:

Column D

John
Peter
Mary
Andy
Dave
Karen

Couple of things to consider:

1. The user would most likely have to confirm this function with Ctrl+Shift+Enter --> that is fine

2. A UDF might not be the most efficient/suitable solution to this "problem", but I'm currently learning about UDFs, so am more interesting in how they work, than determining the most elegant/correct way to resolve this issue.

3. The order of the resultant values which I have used above (i.e. reading the range passed to the UDF from left to right one row at a time), is not critical, if it is easier to read top to bottom one column at a time, that would be absolutely fine too.

View 7 Replies View Related

Look Up One Value And Return Multiple Values

Apr 20, 2006

I'm trying to lookup one value in a column, and if I find that value in multiple cells, I want to return what value is next to the finding value.

Attached is an example with 2 tabs of info.

View 9 Replies View Related

Vlookup To Return Multiple Values

Jul 6, 2007

Is there a way where i can vlookup a column and return all matches if there are multiple values?

View 5 Replies View Related

VLOOKUP To Return Multiple Values?

Jun 27, 2014

I have a list of countries in column A, there are duplicates in this list. In column B are numbers corresponding to the country.

A B
IRELAND 1221
GERMANY 2341
USA 3443
IRELAND 2231

Ireland could be in the list and have up to 20 different values beside it in column B

On a separate sheet i have a drop down list of all the countries. I am looking for a vlookup or something similar to return a list of all the numbers beside IRELAND when Ireland is selected in the drop down list.

I had been trying a vlookup but this looks at the list and stops at the first "IRELAND" it finds and just returns the value beside the first "IRELAND"

View 3 Replies View Related

Vlookups That Return Multiple Values?

Dec 17, 2013

I am trying to write a formula that is based on a vlookup that will return multiple values. Attached is a spreadsheet that is an example for what I am trying to do. On the download tab it is an example of a general ledger. On the total tab I want to enter a GL# and have it list all the entries for that GL number. I know I can try an array, but I have not been able to figure that out.

View 6 Replies View Related

Return Multiple Values From Array

Dec 27, 2013

I am remodeling my home and have put together a budget template. I have uploaded the file to make things easy to understand. Basically I want to return the values from column F and G on sheet "ledger" to the corresponding account name to column H in sheet "budget". For example, rows 14, 21, and 22 in the "budget" sheet currently correspond to the "fuel" account. I would like those values in column G (or F) to be added and calculated in cell H58 in the "budget" sheet.

View 5 Replies View Related

Return Multiple Values Horizontally?

Jun 5, 2014

I managed to found an array formula that return multiple values with a condition. Below the formula:

[Code]....

I am attaching a simple file. In cell O4 I paste the formula, and I drag it as much as I need. But my issue comes when the formula begins the calculations. I need to calculate 50 columns with over 9,000 rows each column (over 45,000 arrays). Imagine the processing load to my computer and the consuming of time.

Attached File : Return-multiple-values-horizontally.xlsx‎