Looking Up 2 Columns To Return Value From 3rd

Oct 7, 2011

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.

View 3 Replies


ADVERTISEMENT

Matching Two Columns To Return One Value

Mar 7, 2014

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?

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

Return The Value Based On Two Columns

Feb 24, 2014

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.

I have attached a sample workbook.

View 8 Replies View Related

Return The Value From The Columns Headed Value Y

Oct 14, 2008

I have a large table of data:

A y y y y y y
x b b
x c c
x d

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.

View 9 Replies View Related

Return A Value From One Of The VALUES Columns

Nov 26, 2008

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.

View 9 Replies View Related

Compare Two Columns And Return Value

Aug 4, 2006

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.

View 4 Replies View Related

Compare 2 Columns & Return In Third

Jun 1, 2007

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.

View 9 Replies View Related

Vlookup To Return One Value From Multiple Columns?

Sep 21, 2013

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.

View 9 Replies View Related

Match Data From 2 Columns And Then Return Value From A 3rd One

Oct 4, 2008

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.

View 2 Replies View Related

Vlookup From Multiple Columns With Only One Return

Dec 19, 2008

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?

View 4 Replies View Related

Return Max Value Considering Criteria In Multiple Columns

Jan 10, 2014

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

View 2 Replies View Related

Check Two Columns At The Same Time And Return The Value In The 3rd

Aug 6, 2009

Is there a good way to check two columns at the same time and return the value in the 3rd?

Example

12 0.07 8
12 0.37 10
12 0.82 12
12 1.30 15
12 3.90 22
12 8.00 28
12 15.00 35

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.

View 2 Replies View Related

Matching Data In 2 Columns To Return 1.

Feb 19, 2008

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

=SUM(IF(AND(C3="x",P3="d"),1,0), IF(AND(C4="x",P4="d"),1,0), IF(AND(C5="x",P5="d"),1,0), IF(AND(C6="x",P6="d"),1,0)), IF(AND(C7="x",P7="d"),1,0)), IF(AND(C8="x",P9="d"),1,0)), IF(AND(C6="x",P6="d"),1,0))...

However, this formula is way too long. Is there another way of writting or simplifying this formula?

View 13 Replies View Related

Lookup Multiple Columns Return A Value

Jun 26, 2012

Here are two sheets:

Sheet1
systemip1 ip2 ip3 ip4 ip5 ip6
system11.1.1.11.1.1.21.1.1.31.1.1.41.1.1.51.1.1.6
system22.2.2.22.2.2.32.2.2.42.2.2.52.2.2.62.2.2.7
system33.3.3.13.3.3.23.3.3.33.3.3.43.3.3.53.3.3.6

Sheet2
ip system
1.1.1.3
2.2.2.3
3.3.3.6
3.3.3.1

Sheet 1 has 7 columns(system,ip1,ip2,ip3,ip4,ip5,ip6 and ip7)
Sheet 2 has 2 columns (ip,system)

I have to fill column "system" in sheet 2 with "system" listed in column 1 of Sheet1.

In other words look for "ip" in Sheet2 in 6 columns of Sheet1 and return column 1 of sheet1 as value.

View 5 Replies View Related

Lookup Two Different Columns And Return If Both Found

Dec 3, 2012

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.

View 6 Replies View Related

Compare 2 Columns And Return 3rd If Match?

Jul 4, 2013

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.

View 3 Replies View Related

Compare Two Columns And Return Difference

Sep 25, 2013

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.

=VLOOKUP(B, A, A, FALSE)

Is there a function better for this.

View 1 Replies View Related

Two Columns Have Data Is Not Similar Then Return The Value

Jun 13, 2007

I need a formula that looks at 2 columns and if the data is not similar to return the value.
For example
Column A
joe Bloggs

Column B
Bloggs, joe

Column C
if value in column B is not similar to column A to return value of column B

View 9 Replies View Related

VLOOKUP Return All Columns In The Range

Aug 31, 2007

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?

View 9 Replies View Related

How Do I Lookup Based On 2 Columns And Return A 3rd

Nov 30, 2009

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.

View 9 Replies View Related

Lookup Multiple Columns And Return Top Row

Sep 21, 2006

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.

View 9 Replies View Related

Lookup Across Columns, Return First & Last Value And Offset

Mar 29, 2008

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.

View 9 Replies View Related

Compare 2 Columns And Return Matches

Aug 12, 2008

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.

View 5 Replies View Related

Compare Two Columns And Return Match From Third Column?

Mar 5, 2014

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.

View 14 Replies View Related

Return Value In Third Column If Values In First Two Columns Matching?

Mar 20, 2014

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.

View 4 Replies View Related

Find Duplicates From Multiple Columns And Return A Value?

Jun 9, 2014

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?

View 4 Replies View Related

Compare Columns In Worksheet And Return Row Value For Matching ID

Jul 17, 2013

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.

View 2 Replies View Related

Loop Through Rows And Return Matching Columns

Jan 9, 2009

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.

View 9 Replies View Related

If Two Columns Match - Row And Column In Another Sheet Return A Value

Jan 11, 2013

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.

View 9 Replies View Related







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