I have to lookup values from 2 columns and return a value from the 3rd col. I went through previous posts on this forum and was able to get the values. But I corrected the data and am trying to do this again but I only get the #N/A response.
Here's what my data looks like:
Col F Col G Col O
Unique ID Drug Choice
3.1 Heroin 2
3.2 Cocaine 4
3.3 Beer 0
4.1 Heroin 3
4.2 Cocaine 3
4.3 Beer 1
And herez the formula I am using
INDEX($O$2:$O$2277,MATCH($P$2,IF($F$2:$F$2277=Q2, $G$2:$G$2277),0))
where O2:O2277 is the col with the Choice values.
P 2 corresponds to Heroin that I have to look for in Col G and F column corresponds to the Unique ID.
Alright, so I need data in column A(Last names) and data in column B(First name) to match their respective names on another sheet. When a match is made, it will return column C(Hours worked).
I originally used Vlookup to match last names, something akin to =VLOOKUP(A:A,Week1!A:P,4,FALSE)
But this does not account for employees that may have the same last names. Is there a way to combine two vlookup's? Or is Index a possible solution?
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
I need a vba code to return the value if two column's values are in sheet1 matching into sheet2,
Let us assume that, I have two sheets in a workbook(Sheet1 and Sheet2), In Sheet1, Col A and B are there some value and same like in Sheet2(Additionally Result in Col C)
I want to return the value in Col C of Sheet1(till A2 to last used rows) from Sheet2(Col C), if the first two column's values are matching with both sheets.
In the column headed 'A' I am wanting to return the value from the columns headed 'y'. There are 40+ columns and 1,500+ rows of data so I don't particularly want to do this manually (understandably).
Just for background the rows are lists of properties, the cells are populated with the name of a contractor, and the Ys represent different types of work.
I have 8 columns of data, the first 4 and the last four columns have the values which correspond to each other.. Here is an example:
VALUES(A to D)NAMES(A to D) ABCDABCD 1.3451641.3383581.286221.359808321325370391
So value 1.345164 corresponds to 321 and 1.28622 to 370. I need a function that will return a value from one of the VALUES columns which corresponds to the second smallest cell value from the NAMES columns. For example – here the second smallest number in NAMES column is 325 so the function would return the corresponding value of 1.338358.
I have two columns with the same data type (text) and I want to compare one column to another and if they match, return the data from another column.
For example: I have 2 columns of names on separate Excel files. One file contains name and phone numbers, while the other doesn't. I want to compare the names from file one with file two and if the names match, return the corresponding phone number in a new column.
I've tried VLOOKUP and IF statements, but I can't get it to come out.
I have a sheet with 2 big columns (3000 each). I want to search each element from the first column in the second column and put a message "Yes" if it is and "No" if it isn't, at the same position in the third column. How can i do that? I want to use the sheet for more than 3000 lines (about 5000). I attached an example.
I have two separate worksheets, and I am trying to create a Vlookup or Index and Match formula. Here is the example:
Sheet 1 Cell A1= Employee ID: 123-D.
Sheet2 Vlookup A1 from Sheet 1, and match the first five characters to Column A, Column I and Column P. If a match, return name (e.g. John Doe) in Sheet 1, cell B1.
1. i am comparing text 2. i have >100 countries, and with vlookup i have to type every single one, and this is what i want to escape (mb i need to refer to an array, or string, or no idea) 3. mb a solution could be sth with index, or match, i just tried it, copying some similar formula from www, but it didn't match the countries, and return the value respective for that country.
E.g. I have China in col1 (1st file), #employees 10 in col2 (1st file), and China in Col 1 in 2nd file. So, a formula should match China & China from both files, and return 10 as number of employees.
I have a spreadsheet with twenty columns. Column A has an item number (say "Clutch"), and the remainder of the columns have values. However, there only be one column in the range B:T which will have a value on the same row as "Clutch" (say "Black" in column "N").
How I can I return "Black" using a vlookup or should I be using something else?
I would like to return the value in column D (Store Name) that corresponds to the Max value in column N (Units Still Required). However, this Max value must meet certain criteria. That is, the State (column J) and Style Code (column Q) must be the same as that of the row being considered.
I have tried the below formula, and it appears to work the majority of the time, however, occassionally it does not adhere to the criteria (i.e. same State and Style Code).
For example in cell M7: =IF(L7=0," ",INDEX(D$7:D$999,MATCH(MAX((IF((J:J=J7)*(Q:Q=Q7),N:N))),N$7:N$999,0))) CTRL + SHIFT + ENTER
I look for 12 in the first column with vlookup but I need to also look for a figure in the 2nd column which is greater than my figure. So say I want 12 & 6.0 I would want the formula result to be 28 because the nearest larger number in the table column 2 is 8.00.
I'm working on a excel sheet to compare data of 2 columns. One column (or array), let's say C3:C13 contains "x" in some of its cells. The other column, let's say P3:P13, contains "d" in some of its cell. What I would like to do is compare the cells of the same row, see if they contain any data and return 1.
So if C3 contains an "x" and P3 contains a "d", therefore it returns 1. If C4 doesn't have an "x" and P4 has a "d", it will return a 0.
The closest I can get to this is the following formula
I am trying to look in B1:B100 for the Cell that matches A1 and C1:C100 for the cell that matches A2 then return the value from D1:D100 that meets the 2 criteria.
I have 2 columns of usernames A and B, A has approx 700 usernames and B has about 80. In column C I have a list of names 1 for each username in column A. I want to compare the B with A and if there is a match return the value in C and place it in D.
I have two columns of values and I want to compare them and return differing values. So I named the ranges "A" and "B" so if your in B but not in A I want that value in C.
I started with a vlookup but that doesn't seem appropriate to the way I was using it.
I wanted to lookup value A5, and return all columns in the range A1:D5, I could use the formula =vlookup(A5,$A$1:$D$5,2,false) and copy this formula 3 times changing the third parameter 2, to 3 , 4 and 5. i.e 4 VLOOKUP formulas.
I could, instead, convert the VLOOKUP into an array and enter it CSE i.e =vlookup(A5,$A$1:$D$5,{2,3,4,5},false). This returns the right answer but "appears" to have only used 1 VLOOKUP.
What if this was extended to say 26 columns so instead of "hard coding" the array constants I used =vlookup(A5,$A$1:$D$5,COLUMN(B:AA),false). Is this better than 26 indivisual VLOOKUP's in term of speed?
I have 2 worksheets with thousands of rows. I need to lookup 2 columns (customer account # and number of occurrence) and then return a 3rd column (type of occurrence).
How do I do this? Normally I use sumproduct but in this case I need the result to be the text value for the type of call, not a count.
I've been working this for ever and can't seem to figure out the best way to go about it. I have attached an example sheet. All I need to do is figure out the Dept #... which is listed in Row 1, Column F:H. I want to match the project numbers and then return the AA, BB, or CC in Column B.
I have a data range set up in a step chart formation. This means there is a "Name" to the left and a time line of "values" to the right in quarterly increments (3 months at a time). Row 1 has the first month of each quarter and should be used to find the start date. Row 2 has the last month of each quarter and should be used to find the end date.
I need to find the first value from left to right and return the date directly above it in row 1.
Then....
I need to find the last value in the step chart and return the date directly above it in row 2
Example Start Date..A...B...C....D...E...F End Date....U...V...W...X...Y...Z Value-1...........5...2....7........
Value-1 Start Date: B End Date: X Duration: = B - X
The reason i need this information is to create a Gantt Chart.
I have two columns, one with the target text (D:D) and one with text I want to compare it too (A:A). If the text in column (D:D) matches the one in (A:A), I want to return the value in (B:B). I attached a sample of the problem i'm dealing with.
I must match column A and column B to return the column C In colA there are all my countries In column B there are all my countries code and countries I want get the colC where all my colB match than colA.
I have a two different workbooks book 1 and book 2 with some data.
If values of first two columns ( Column A and B) of both workbooks are matching then I want a formula that can return the value of third column (column c) of book 2 in book 1 column c.
example:
Book 1
column A / Column B / Column C
100 / 200 /
Book 2
Column A / Column B / Column C
100 / 200 / 300
now I want a formula that can return in column C of book 1 to display 300.
And the data runs across some 100 rows in both workbooks.
My spreadsheet has multiple "sessions" by date and each has three columns: a name, their organization, and a column where we want to display an "R" if they are a repeat participant. Each new session is entered to the right of the last. The names are in every third column. Like so:
name company R name company name company name company R
Is it possible to search through the whole document to find repeating names, and then display an "R" in every third column if they are a repeat participant?
I have used the merge facility to incoporate all the worksheets into one excel files. As they all have same heading, I have sorted the data, first by their account expiry time (oldest to newest) and then by manager (A-Z)
For example, system report generated on 15/03/2013, 28/03/2013, 03/04/2013, 15/05/2013, 28/05/2013 and so on and these are sheet names too. What I want is one worksheet called report with the same heading as my merged worksheets and return values where Column C in 28/03/2013 is compared to 15/03/2013 and if the employee ID matches than return the whole row of data for the report.
the next query would then be for finding employee ID in 03/04/2013 and comparing it with 28/03/2013 worksheet and returning the matched ID in report worksheet.
This is the layout of the report worksheet.
Display NameEmployee IDAccount StatusE-mailDepartmentManagerComment 15/03/2013 to 28/03/2013 28/03/2013 to 03/04/2013 03/04/2013 to 15/05/2013 15/05/2013 to 28/05/2013
so for the above report worksheet, if employee id matches the two compared worksheet (15/03/2013 to 28/03/2013) return all value (Display name, employee id, account status, email, department, manager, comment) from the 28/03/2013 and so on.
I have a worksheet with 2 columns. The first column contains names and the second column contains numbers associated with the name.
How can I specify a name, go through the sheet to find all the occurances of that name and then paste all the numbers associated with that name in another worksheet in a set position.
I have a file with multiple column headers and data entries (dropdowns) into the rows. I wanted to create a secondary sheet...like a pivot table..if the vertical and the horizontal are both in the two columns selected it returns a value in the table.
I would use a pivot table, but it is a data entry sheet and the pivot table does not bring up all the horizontal and vertical values unless they have already been selected in the sheet.
