How To Return Mulitple Corresponding Values

Sep 8, 2005

i want to look up a name that occurs several times in one column of a
spreadsheet and return corresponding values from each row the name occurs on.
Vlookup returns only one value. How can I get multiple values?

View 14 Replies


ADVERTISEMENT

Search In Mulitple Columns And Return Corresponding Column/Row Result

Apr 30, 2008

Basically, I need to look for an item, lets use "apple" as an example, in a range of cells, A1:D100. Then the corresponding result would be in another column in the same row.

View 10 Replies View Related

3 Way Switch Or Change Values From Mulitple Places

Dec 29, 2006

On sheet 1 there is a dropdown box that indicates a condition. For example the choices are 1-4. On Sheet 2 calculations are made based on the condition in the drop down box.

I also show the condition # on sheet 2 with a simple =sheet1!a1. What I would like is the ability to have the drop down box on both sheet 1 and sheet 2 and that if you change the value in either place then is changes in the other. Same way that you can operate a light with 2 different switches.

View 9 Replies View Related

Mulitple IF Statements Regarding One Cell

Dec 4, 2008

I am trying to using multiple if staements regarding one cell. What I am trying to achieve is:

if b2=0, then null, however if b2=1, then arable, however if b2=2, then manged grassland, however if b2=3, then forestry/woodland, however if b2=4, then semi-natural vegetation, however if b2=5, then urban, however if b2=6, then water

I have these 2, but they don't work - what am i doing wrong?

=IF(B2=0,null, IF(B2=1, Arable, IF(B2=2, Managed_Grassland, IF(B2=3, Forestry/Woodland, IF(B2=4, Semi-natural_Vegetation, IF(B2=5, Urban, IF(B2=6, Water, NO_LAND_USE)))))))

=IF(B2=0,IF(B2=1,IF(B2=2,IF(B2=3, IF(B2=4,IF(B2=5, IF(B2=6, Water, null), Urban),Semi-natural_Vegetation), Forestry/Woodland), Maneged_grassland), Arable), null)

View 5 Replies View Related

Mulitple Action When Condition Met

Oct 21, 2008

How do you set the code so that, if a condition is met, then several cells are filled with values?

So far, my code is

HTML If Cells(i, 2) < Cells(i, 4) Then
Cells(i, 5) = "A"
Cells(i, 6) = Cells(i, 2)
End If

View 11 Replies View Related

If Not Exist Add, If Mulitple Delete..

Jan 14, 2009

I have a worksheet that has three columns, TYPE, VALUE, PERIOD.

Within TYPE column it will always be either A or B
Within VALUE column a number common to both A & B for that "period".
Within PERIOD column a month-year.

A shown here:

TypeValuePeriod
A3000Jan-09
A3000Jan-09
A3000Jan-09
A3000Jan-09
A3000Jan-09
B3000Jan-09
B3000Jan-09
B3000Jan-09
A3200Feb-09
A3200Feb-09
A3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09....................

View 9 Replies View Related

Mulitple Lookup Criteria

Jul 30, 2008

I have a report that I paste into excel. The report contains 4 columns. Column A contains a numeric value. The numeric value relates to a geographical area and the same numeric value may show up more than once. The reason the numeric value may show up more than once is because in column B there is a list of dates showing when a percentage in column D become effective. I have attached an example of the report.

What I am looking for is a was where the user can enter a geographic number in cell J4 and a control date in cell J5 and have cell J7 display the percentage value. I need the lookup to look up the correct area and then use the control date to find the corresponding percentage. So if Area 1 (numeric value) shows up in Column A in rows 2 through 5, the control date will determine which row to pull the percentage from using the effective dates in column B.

View 3 Replies View Related

Combo Box To Remove Mulitple Rows

Jan 12, 2010

I am attempting to design 2 combo boxs by which you can select from a number of equipment types and a criticality:

Equipment Types Criticality
Vac Pump 1A
Filter Dryer 2A
Valve 3A

I want to attempt to write a macro so that when the equipment type and criticality has been selected, a corresponding number of rows will disappear.

For example:

If Vac Pump and 1A are selected then all the rows from 70 downwards will be hidden, and if Filter Dryer and 2A are selected, then the rows from 6 to 38 and from 47 onwards will be hidden.

View 10 Replies View Related

Inserting Mulitple Rows Using Macro

Mar 18, 2009

I'm trying to write a macro that inserts multiple rows by giving user a MsgBox to enter the # of desired rows. Meanwhile, I need to make sure that the formulas in certain cells are filled down (not just copied from cell directly above). I put something together but it's only adding a single row. Attached is my sample workbook.

View 2 Replies View Related

Highlighting Mulitple Cells With A Macro

Nov 6, 2009

I need to highlight cells for all of the products and rates I need to get pricing for. I have a macro that goes out to my data and pulls in each different rate and program (columns BM:BN). That macro also sets the first note rate in the range to the lowest of the group, so this will always be different.

I now manually highlight each cell that I need to get pricing, but would like to create a macro that looks up those codes and rates in column BM:BN and highlights the cells for me. I need it to highlight in all 7 boxes. I've attached a sample worksheet of what it looks like after I highlight the cells.

View 2 Replies View Related

How Do I Link Mulitple Drop Down Boxes Together

Feb 20, 2006

I have 2 drop down lists made, but I want to have one linked to the other
one.

For example the first list has types of wood in it.

Bamboo
Maple
Oak

The 2nd one has pricing in it.
1.25
2.50
3.50

If I click on Bamboo - I want the 2nd list to auto generate 3.50. Can that
be done?

View 14 Replies View Related

Retrieve Latest Value From Mulitple Columns

May 4, 2008

I have a list of teams in 2 columns (columns A and B). These teams play matches against each other. The teams repeat themselves but vary in their order in which they play and whether they are home or away; i.e. appear in column A or B.

Each team has a points brought forward value in column C or D which correspond to the teams in column A and B respectively. They also then have a carried forward points value in column E or F produced after the points from the current game (not shown in the attached file).

I need to be able to find a formula which will retrieve the latest (last match) carried forward value for a team and enter it into the brought forward cell. This is made difficult because the team may last appear in columns A or B and there is no logic as to when they last played. It is easy enough to use lookup to find the value from the first row of an array but i cannot work out how to find the value in the last row.

View 10 Replies View Related

VBA Copy Workbook To Mulitple Workbooks

Apr 25, 2007

I am attempting to create a macro that does the following. I need to copy the active workbook to 25 new or existing workbooks without showing "save as" etc. These workbooks will reside in the same directory as the active wb. Problem is, I have multiple directories that I will need to do the same action.

C:ClientClientNameExample1.xls (only has sheet1)
---This will be the master which needs to be copied to 25 more times like below.

C:ClientClientNameExample2.xls
C:ClientClientNameExample3.xls
C:ClientClientNameExample4.xls
C:ClientClientNameExample5.xls
etc...

View 9 Replies View Related

Macro To Search Particular Column On Mulitple Worksheets

Sep 15, 2009

I am using the code below to search my entire workbook for a value entered by the user at a popup.

View 2 Replies View Related

Data Search & Copy From Mulitple Worksheets

Dec 31, 2009

I have a workbook with 26 multiple worksheets that each represent 1 of 26 payperiods per year (bi-weekly payperiods). These worksheets consists of rows of employees names and hours worked each day. The columns consist of dates representing each day in that payperiod.

I need to be able to search all the worksheets (ie. search all 26 payperiods) to find specific 30 day intervals and then copy each employees hours worked to a master spreadsheet. I have tried variations of Index & Match formulae as well as vlookup/hlookup/ISNA without luck. I am not able to use VBA.

View 5 Replies View Related

Select Mulitple Sheets Without Naming Each Sheet

May 14, 2006

Im sure there is a post somewhere on this forum as I have seen it before but I cant find it anywhere. All I want to do is be able to select Sheets Sun,Mon,Tue,Wed,Thu,Fri,Sat without naming each of the sheets. The post I seen would select all the sheets between the two sheets that were stated in the vba code. something like: Sheets( Array("Sun" To "Sat").select

View 5 Replies View Related

Bringing Data From Mulitple Worksheets To A Master Sheet

Jun 10, 2008

Each salesman has an enquiry log / hot prospect sheet that they are responsible for.

At present at the end of the month our receptionist has to consolidate this information manually onto a master sheet.

I would like to create an automatic master sheet that auto updates each time it is opened.

It has to read data from about 13 work books each with varting amounts of date. All works books are formatted identically (or they will be)

View 14 Replies View Related

Import Mulitple Single Column Text Files

Apr 20, 2008

I am trying to get a VBA code to import multiple text files into an excel worksheet. The text files contain about 5,000 words each listed in a single column. I found this thread Import Multiple Text Files and the code listed there:

Sub test()
Dim myDir As String, fn As String, ff As Integer, txt As String
Dim delim As String, n As Long, b(), flg As Boolean, x, t As Integer
myDir = "c: est" '<- change to actual folder path
delim = vbTab '<- delimiter (assuming Tab delimited)
fn = Dir(myDir & "*.txt")
Do While fn <> ""
Redim b(1 To Rows.Count, 1 To 1)
ff = FreeFile
Open myDir & "" & fn For Input As #ff
Do While Not EOF(ff)..........................

View 8 Replies View Related

Lookup All Values In Ascending Order And Return All Corresponding Values.

Oct 23, 2008

I have a problem with the formula that lookup all values in ascending order and returning all the corresponding values. eg: I was intended to lookup for the value in ascending order under the Total Occurrence and returning all the corresponding value under the Nos Group but encountered the same Nos Group was returned when there is same value appeared under the Total Occurrence.

View 3 Replies View Related

Look Across Values In 12 Columns, Return The Number Of Unique Values

Apr 17, 2009

I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows.

What would be the best way to do so?

Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns.

I have attached an example file that represents how the data is stored and the output I would like (Unique Count).

View 7 Replies View Related

Return The Values And Add Missing Values As Zero?

Mar 12, 2014

I have some value sorted numerically (according to ID column) but there are some missing values. In a new column (Desired value column), I would like to return the values (according to ID column) and add missing values (according to Order column) as zero.

View 2 Replies View Related

Return Values Of Adjacent Cells When Condition Is True Omitting Values Where Condition Is False?

Jan 15, 2014

Here's a simplified example:

ColA
ColB
ColC

Row1
A
Y
A

Row2
B
N
D

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

I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.

The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.

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

Return The Corresponding Name For Each Of The Values

Jul 9, 2007

I have a matrix of staff names, listed down column A, and particular tasks listed along the top (cells B1, C1, D1, E1 etc). I have another matrix that contains the same data but reversed i.e.

In the central cell array there's everyone's scores, relevant to the particular task (ie, where the intersection is)

On a separate sheet, I have a drop-down list that contains the list of tasks. I would like a formula that can take the task shown in the drop down list, search in the matrix of data and find the top 10 scores for that task.

I also need a formula to return the corresponding name for each of the values that the top 10 search returns.

In the case of more than 10 people sharing the same score, I need the formula to return them all, irrespective of whether there are more than 10. Rather than the function stopping when it reaches 10, it should continue and return all the people with the particular score.

View 9 Replies View Related

IF - Return Values From Lists

Jun 21, 2013

I have 4 lists:

Job #
Job Title
Client
Job Location

I would like to select the Job # in Cell B2 (Job # list) and have Cells C2 filled in from Job Title List and D2 Filled in from Client List and E2 filled in from Job Location list.

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

Return Values From Several Columns

Feb 19, 2009

If I am creating a vlookup forumulae, and I want to return values from several columns, I have been manually changing the column it's returning the value from when I drag the formula across the columns. This is ok for a few columns.

eg =VLOOKUP($D2,$AB:$AJ,1,FALSE), dragged across columns, and then manually edited in each column to read

=VLOOKUP($D2,$AB:$AJ,1,FALSE), =VLOOKUP($D2,$AB:$AJ,2,FALSE), =VLOOKUP($D2,$AB:$AJ,3,FALSE), =VLOOKUP($D2,$AB:$AJ,4,FALSE)

etc

The value doesn't increase when you drag it.

is there a short cut for doing this automatically?

View 10 Replies View Related

If Statement To Return 3 Values

Aug 25, 2013

I want to use an IF statement that returns 3 values. I can do it to return 2.

Example: I am measuring performance of individuals. If they deliver below 50% I can return the value "Needs Improvement". If they deliver Over 60%, I need to show "Excellent" and finally if they deliver between 50% & 60% I need to return the value "Good".

I am not sure how to show 3 values.

View 3 Replies View Related

Find Top Ten Values And Return Value From Another Row?

Nov 23, 2013

I have a huge data base (daily temperatures dating back to 1872), but for simplicity I have limited the values to just a month for this question. What I want to do it list the ten warmest temperatures and then return the date in which it occurred. I am using the following LARGE function to get the ten warmest temperatures (Column B - high temperatures).

=LARGE($B$2:$B$31,$D2)
Column B = High Temp in Data Table
Column D = Rank in Results Table

This works great. However when I use the following Index function to get the date in which it occurred (Column A - Date), it keeps returning the first date in which it occurred when there are multiple occurrences of the same temperature.

=INDEX($A$2:$A$31,MATCH(LARGE($B$2:$B$31,$D2),$B$2:$B$31,0))

Column A = Date in Data Table
Column B = High Temp in Data Table
Column D = Rank in Results Table

In some cases, I have more than 2 occurrences of the same temperatures. For example, the high temperature of 23 degrees occurs 3 times during the month (1/4/2013, 1/11/2013, and 1/12/2013), but only the first one 1/4/2013 shows up in my results table. I would like the other dates to show up. These tables are listed below.

Data TableResults Table
DateHigh TempRankHigh TempDate
1/2/201311271/5/2013

[Code].....

View 3 Replies View Related







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