Lookup In One Column, Use Data In Next

Dec 12, 2008

I have data in one column which is text based (A, B, C, D, E) and in the accompanying column is a response (1, 2, 3, 4, 5).

I want to be able to find those who answered 1 and then group to letters - so if 'A', 'D' and 'E' replied '1' then I want the result of the formula to be ADE.

I have been doing this manually and just typing the results in from a print-out but am hoping I can automate and get Excel to do the work for me to eliminate inputting errors!

View 9 Replies


ADVERTISEMENT

Lookup Up/Match: Return A Value In Column A If My Data Matches Column B?

Jun 5, 2009

I have a tab that has 2 columns of data and I want to be able to return a value in column A if my data matches column B. If column B has the text TRUETRUE, I want to bring back the corresponding data in Column A. How do I return all the data in Column A for all the TRUETRUEs in column B? I can only get the first instance of TRUETRUE.

View 4 Replies View Related

Lookup Or Data Change Based Upon The Data Reported In Column

Mar 7, 2008

I need to pull info to additional tabs. The problem I am having is that the data is sorted daily based upon performance numbers, so the REP names in column A can change based upon the data reported in column C.

What I want to accomplish:

Pull daily data for each unique name in column A to a team tab/worksheet that will be used to report that team's daily data.

Below is a representation of the data I need to break down by Rep Name and Team.


Rep DATA DATA DATA DATA DATA DATA DATA
1 2 3 4 5 6 7 REP 2 27.5% 14


51 86 REP 1 33.3% 14


42 74 REP 3 19.3% 11


57 86 REP 5 19.6% 9


46 66 REP 4 33.3% 9 ...........................

View 9 Replies View Related

Lookup & Return Data From Different Column

Apr 8, 2009

I have a book with multiple tabs in it. The first is a master customer file, including customer numbers. The next two tabs include multiple references to the same customers because they list multiple shipping addresses and contacts for each customer on a separate line. I want to be able to have the additional tabs refer to the master list and find the customer name, then return the corresponding customer # in the previous column for that customer. This is what I came up with: =IF(B:B=Sheet2!B:B,Sheet1!A:A,0). But the results simply returns numbers that increase by 1. It should repeat the same customer # for the number of times that customer name is listed based on how many addresses or contacts there are. What am I not doing right?

View 9 Replies View Related

Lookup Of Row (return Data From Specified Row) With Column Heading And Value

Feb 21, 2014

Encryption algorythim.xlsx

Here is my scenario I have all this data i want to search in. In the rows there are repeating values and in the columns they do not repeat.

As seen in the attached i am able to give a column and row by title and then get the intersecting cell data from that. (Encryption lookup)

What i want to do is give the column tittle and row value and get back the row title. (Decryption Lookup)

This shouldn't be too hard seeing that the data in the columns has no repeats.

View 6 Replies View Related

Lookup Data In Array, Results In Column 1

Feb 23, 2009

I have a table of data with years in row 1, week # in column C. The data array is from C2:O34.

I want to find the max value in each year column, then look up that result and return which week that value occured.

Is there a vlookup that allows you to specify which column of the array has the lookup value and which has the lookup result?

View 3 Replies View Related

Lookup Data Without Knowing Return Column

Aug 25, 2006

Got a bit of a stinker here. (Well, it is for me anyway. I haven't used Excel in months)!

I want to use a VLOOKUP function (I think) to retrieve data from a list of Exchange Rates on a separate sheet.

The user enters a 'Period' number, selects an 'exchange' variable (the blue text cells). The hope is to lookup the data on the 'FX RATES 06-07' sheet and return the result in the green cells.

I've tried using VLOOKUP but one of the arguements specifies that you nominate a column to return the information - that's the problem, we don't know that information when asking Excel.

View 4 Replies View Related

Lookup In Another Sheet And Get Top Cell Data Of Matching Column

May 9, 2014

Code:
Private Sub GetColData()
Dim SrchRng As Range
Dim SrchVal As Range

[Code]...

And it is also possible by using Column Number to retrieve the data SrchTxt, which is in a cell in the Row, in which "Roy" is found. This Row in this example is Row 5. Column Number may be anything within the search Range. Column Number is 3 in this example.

In Sheet1 "Poet" is in C1. (SrchTxt)

How to retrieve data of the cell in Row 1 in the column where "Roy" is found?

View 1 Replies View Related

Lookup Partial String To Return Data From Corresponding Column

Mar 22, 2007

I have a simple lists containing two columns. One column contains a five digit number and the other a vendor name. The vendor name in most cases is two to four words. I am wanting to type in a partial string of the vendor name and it return to me the 5 digit 'vendor' number.

Col A Col B
20567 3M Electrical, Inc.

I want to type in '3m' or '3M' or 'electrical' or 'ELECTRI' and it return the 20567. The other part of this is that there may be two rows with the same info in which I would need to see both...

Col A Col B
20567 3M Electrical, Inc.
21789 3M Tape Division

Is this possible with standard lookup features in Excel or does someone have a VbScript or macro that will accomplish this?

View 9 Replies View Related

Lookup Row And Column As Criteria But Data Is Spread Across Columns

May 30, 2007

I need to bring in values into one worksheet from another worksheet using row 1 and column A as criteria. I have previously done this using Sumproduct. However the complicated issue with this new worksheet is the setout. The source worksheet (see attached) has the actual data spread across columns. How can I bring in the values to the 'Summary' sheet from the '0607' sheet using the Employee number and the seg code as 2 criterias to lookup and bring in the resulting data (which is spread out in the purple area in the '0607' worksheet).

View 8 Replies View Related

Multiple Lookup Values Rows And Columns To Lookup Single Target Column On Right End?

Apr 7, 2014

I have a table of data (say Column1 to Column 5) with multiple rows.

Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.

I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.

It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.

View 3 Replies View Related

Lookup Column Of Data Match Criteria From Another Sheet Display Items Vertically

Feb 5, 2014

(File is attached here)

I am trying to work on Sheet 2(Details per person). I want to be able to display all items in a row that matches the 2 criteria (Skype ID and Date) and the items are based from Master Raw file which is in another sheet. I would like to just use index and match.

View 3 Replies View Related

Lookup Table (adjust Which Column The Lookup Function Refers To)

Jun 12, 2009

I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.

View 4 Replies View Related

Lookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D

Jan 31, 2008

I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.

I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.

View 9 Replies View Related

Lookup Value In Column & Lookup Value For Offset Column

Feb 14, 2009

I am trying to find a formula that would pick out a row from a matrix based on the values in the first column and then return to me each of the subsequent values from that row. I can't do a vlookup because there are a lot of blank cells and which columns have data changes. I've attached an example. The text in A1 will change and cells A3:B5 will show the corresponding values from that row along with the column title. I would prefer a formula to type in and leave in cells A3:B5 but if that can't be done VBA is okay too.

View 6 Replies View Related

Lookup Function To Lookup For Data In Another Table

Jul 29, 2008

I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.

View 9 Replies View Related

Lookup Data Left Of Found Lookup Value

Nov 28, 2006

here is an example....

(this is on a sheet called Summary)

----A--------B --------C------- D
1Names----At Bats----Hits----Batting Average
2Tom-------38--------31------.816
3Derek------19--------14------.737
4Joey-------40--------28------.700
5Chris-------40--------27------.675
6Chuck------37--------24------.649

Using the LARGE function, Excel has created a list based on batting averages(on a separate sheet called Line-up). It looks like this.....

---A----------B
1Names----Averages
2 -----------.737
3 -----------.700
4 -----------.675
5 -----------.816
6 -----------.649

The problem I am having is figuring a way for Excel to also bring the corresponding names (after using the LARGE function to create the line up list).

View 3 Replies View Related

Lookup Matching Data & Subtract From Another Lookup

Jun 12, 2007

I have a very large spreadsheet of customer information(I call it the master spreadsheet). Each row contains only 3 things: Account number, product bought, Price

Later I receive the money from the customer for that product(the pay sheet) that contains the exact same thing in the same order: Account number, Product bought, Price paid.

What I'm trying to do is compare the two spreadsheets so that when i receive the pay sheet of cusomters who have paid with the amount it will deduct it from the master sheet.

So it should compare account numbers when it finds a match then it should subtract the amount paid (column C) from the master spreadheet price column(column C also).

sometimes customers don't pay the right price so it has to be a subtraction so I can see if it was over paied, underpaid etc.

Right now I'm still doing it manually combining the two documents sorting it by account number and checking for matches in column A (account number).

View 5 Replies View Related

Lookup Multiple Values In Same Column With Same Column Heading

Feb 10, 2010

Is there a formula to isolate observations in the same column (different values) and also all have the same column heading like the file attached?

View 2 Replies View Related

Lookup Formula: Find The Longitude And Latitude Data From My "lookup" Sheet

Jan 28, 2009

In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)

View 3 Replies View Related

Lookup (display The Value In Column B If Column C Is Not Blank)

Oct 31, 2009

What i am looking to do is have column D display the value in column B...if column C is not blank. Here is an example.

View 4 Replies View Related

Perform A Two Column Lookup That Returns A Value From Another Column?

Feb 1, 2010

I have a table with 3 columns. See below for data (ignore dashes):

COLUMN X-----COLUMN Y-----COLUMN Z
Project 1---------21 Dec 09---------$70
Project 2---------21 Dec 09---------$85
Project 3---------21 Dec 09---------$90
Project 1---------1 Jan 10-----------$85
Project 2---------1 Jan 10-----------$95
Project 3---------1 Jan 10-----------$110

As you can see, COLUMN X contains Project Names, COLUMN Y contains Dates and COLUMN Z contains Monetary Value.

Here is what I need to do: I need a formula/function that will lookup "Project 1" in COLUMN X and find the latest corresponding Date in COLUMN Y (for this example, 1 Jan 10) for Project 1 and then display the corresponding Monetary Value from COLUMN Z (for this example, $85).

I have tried using VLOOKUP, but since the data must be sorted it won't work. I tried using INDEX(MATCH), but can't get it to work. My goal is that the cell will be dynamically updated every time a user inputs updated data for a certain project based on the latest date.

View 3 Replies View Related

Lookup In Column A - Select Column B Range?

Feb 11, 2013

I have a sheet that begining in A3 and going down need to look for the First Instance of the text String "Loan Documents" and down to the last instance and select the corresponding range in column B.

Example: if the Text String appears in Range(A14:A32) I need the Range(B14:B32) to be my selected range.

View 1 Replies View Related

Double Lookup (lookup Variable Row And A Variable Column)

Mar 27, 2009

I have a file that I would like to lookup variable row and a variable column. I have tired vlookup and hlookup but these do not work because you have to specify a given column or row versus having that column or row be variable. Is there a way to do this.

Think of a set of times tables. I would like to input 8 and 9 and get 72 as an output. How could I go about doing this?

Or in the attached file, I would like to say A and 15 and get A15.

View 3 Replies View Related

Lookup And Add Value Of Column Next To It To Sum?

Jan 10, 2014

I am trying to find value for a particular date and then sum this value with the value from the column next to it.

I can use hlookup to get the first value but don't know how to include the value of the next column to it.

View 9 Replies View Related

Lookup If Value Is In Column

Oct 22, 2008

I'm trying to create a IF-formula that looks through a column with numbers and if i specific number (in my example the number 7) exist in the column the word PASSED should be written i the cell. I have tried to use =IF(A1:A5=7;"Passed";"") but it does'nt work.

View 2 Replies View Related

Lookup Value In Row And Column

Dec 16, 2009

I am trying to create a formula that will look up the data in Sheet 2, A3 and if found in Sheet 1 to then look up Sheet 2, C1 in Sheet 1 and then enter the data if found into C3. Is there a way to do this?

I have attached a sample workbook.

View 10 Replies View Related

Lookup Value In One Column Result In Another

Jan 26, 2014

In my spreadsheet I have a column (C) that consists of 20 rows. Within this column a calculation is done with only one value (currency) that appears based on the calculation criteria. All other rows are 0 (currency) and the correct calculation can be in any of the 20 rows.

In an adjacent column (say E10) I want the appropriate value to be entered from column C.

View 3 Replies View Related

Lookup On Column And Row Header

Feb 9, 2010

I need a formula to lookup and retrieve data from a table of values. Given an EMP-ID in column A of the row that contains the formula and given a column name in row 1 of the column that contains the formula, I need to do a lookup. So, in effect, I am doing a lookup based on two values .. the column header and the row header.

I have attached a small model to illustrate what I need. Cell C-3 in the first worksheet will contain the formula to lookup and retrieve the value in cell C-7 in the second worksheet, based on the values in cell C-1 (column header = “Database Col 2”) and in cell A-3 (row header = “257”) in the first worksheet.

View 2 Replies View Related

Sumproduct For Lookup Row And Column

Feb 10, 2010

I use the sumproduct for the attached example. I know I have seen this somewhere on the forum where I can get a value based on a criteria from a row and criteria from a column, but I just can't seem to figure it out right now.

View 2 Replies View Related







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