Vlookup Based On 2 Condition

Jan 28, 2009

i looking for function, which will give me a results if its the person good , bad and other (Y). It will look based on column A, but if will be more equal names in this column, it looks in the column surname (B) and return value from column (Y) in this row. But it can be more equal column B.

Here is an example:
Results what i want are in Z1,Z2,Z3

X Y Z
1 sue doe good
2 joe bond very good
3 mark no bad

SOURCE table
A B C
1 john doe good
2 joe black bad
3 sue doe good
4 mark black not bad
5 joe bond very good
6 mark no bad
7 kat doe bad

View 4 Replies


ADVERTISEMENT

Vlookup Using 2 Condition One Based On Devices And Grouping Column?

May 27, 2014

Attach below are 2 sample file and inside it consist of device,grouping and lb1,tb1,ab1. How do I use excel formulae based on 2 condition and set from vlookup with 2 condition file to vlookup with 2 condition 1 file like a vlookup.

View 11 Replies View Related

VLOOKUP Multiple Values Then Give Answer Based On Condition?

Feb 6, 2014

I have been struggling with this formula for ages and have finally given up. What I would like to do is to do a lookup on the concatenated values of Province, Department and Initiative No, which has mutiple values in the status of milestone column, and then give the answer based on a condition.

The condition for this example should be, that if the returned status / or statuses of the initiative is all 1, then the value should be 1, if it's all 2, then the value should be 2, and so on.....but if the values returned from the status of the initiative is a combination of 1,2 or 3, then it should give me the answer 2. If 4 is part of the comination of values returned, then the value should be 4.

Zero - 0 should be excluded from the formula as it is part of planned values and not actual.

View 5 Replies View Related

How To Count Condition Based On Having Another Condition

Apr 12, 2014

So I have one sheet that needs to pull data through to another sheet (which is a stats summary)

I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.

For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.

View 9 Replies View Related

VLOOKUP With A Condition?

Dec 19, 2013

potential VLOOKUP (or possibly another function)

I have a table with about 14 columns, and need a VLOOKUP based on column A.

The problem is Column A has 2 of each unique ID - but column B is the subtype.

For example

Column A Has names (Bob, Jim, Steve, etc) Column B has Opt in or is blank (if they opt out the cell is blank if they opt in the cell says Opt in)

So essentially it's like a vlookup - as i need to read across the columns based on the name, but i also need to check column B if they have opted in.

View 1 Replies View Related

Vlookup With Condition

Jan 28, 2014

See attached the excel for reference : Vlookup with condition.xlsx‎

I have table A1:E10 , where i enter Internal team transfers "From Team" to " To Team" with Month

Now what i want in Table G1:S15 is that whenever a transfer takes place, for that particular month, tables gets filled up as +1 for "To Team" and -1 for "From Team." Also if there are multiple transfers happening for a particular team in a month then that numbers should sum up.

For your easy understanding, i have filled the table as per my logic explained above.

View 7 Replies View Related

Vlookup Rows With Condition?

Apr 10, 2014

I have an excel file with thousands of rows and I'm trying to pull certain data from one tab to another. In the example, I want to pull all Listings that are "Open" (I know, just filter), but if the listing is "Open" I want to pull all of the other statuses it once had as well in the new tab. Notice how a listing can have multiple statuses.

I was thinking I could filter by Status (Open) and then do a vlookup (using the listing number as the lookup number), but it doesn't work.

View 4 Replies View Related

Multiple Condition Vlookup

Jan 9, 2009

I've been trying to figure out how to do a vlookup using two conditions. Reading some other posts, I saw this could be done by combining a MATCH function but I'm still having trouble. Here is what I'm trying to do: I am trying to match cash amounts based on two look up values....one being the identifier (cusip) and one being the pmt code. http://i105.photobucket.com/albums/m204/RAK_08/data.jpg

This data is formatted exactly the same but comes from two different sources. I need to match the pmt amounts for a cash reconciliation.

View 4 Replies View Related

How To Use VLookup Return Only Some Condition Satisfies

Apr 26, 2014

I have an excel sheet with lot of data.i want to use vlookup function for getting what cell i want. the same time it will also check if and only if two cells are matching then return the result.is it possible. for example:

101 3.2 7.3 200 500
102 5.2 8.3 200 450
103 3.2 7.3 200 750
101 7.3 7.3 200 450
102 7.3 7.3 200 500

103 7.3 7.3 200 600

if my lookup_value is 103(ie A3 ) in this table array and required column is 6th column(ie E6).

return the value if and only if B & C cells of the corresponding row matches. In the example B6=C6 then return the E6=600.

if i am using without this condition it returns the same row values ie E3=750 required is E6=600

View 12 Replies View Related

VLOOKUP, IF Condition And Pivot Table

Feb 22, 2009

Have following table:
No. Be Country
1 D GB
1 C DE
2 B DE
2 A GB

I want to present it as follow:
GB DE
1 D C
2 A B

Did not solve it with a pivot table. Not either with VLOOKUP combined with IF. I am thinking in the direction: If 1 in the first column and GB in the third, return the D. But I am not getting it right.

View 2 Replies View Related

Adding A Condition To An IF And Vlookup Forumula

Aug 28, 2009

Title: How do I add a condition to an IF and Vlookup Formula:

I need to edit a formula to include a condition. I attached an excel file for ease of explaining the problem. I want to say if $E$5 has “A to B” then F5 will automatically have BOR, but if E5 has anything else then the Vlookup formula applies.

I was thinking something along this formula:
=IF(AND($E$5<>”A to B”,ISBLANK($B5),"",VLOOKUP($B5,'Drop Down'!$D$1:$E$500,2,FALSE)))

I know I need to add “BOR” in the formula above somewhere
Where am I going wrong?

View 2 Replies View Related

Setting Condition Before Running VLookup VBA?

Feb 16, 2014

I created a Vlookup but first want a condition to be met to determine what Vlookup to use.

Such as

If cell = 1 then
run Vlookup #1

If cell = 2 then
run Vlookup #2

There are only 4 possible variables that the cell could equal. This would have to be on a loop, because the entries in the worksheet are multiple. It would first see what the specific cell equaled, then determine what Vlookup to use.

View 1 Replies View Related

Sum Based On Condition

Dec 15, 2009

I want to total column C, but only where column B contains certain data, like add column C where column b contains abd so that i get the answer 7, Hope ive explained it right.

B C
abd 1
abd 2
bmr 3
abd 4
crc 2
crc 3
bmr 2

View 2 Replies View Related

Set A Formula Based On Condition

Mar 22, 2009

I have a column called Average and it have numbers from 0 to 100. I want to set a string on another column based on the value of Average. The condition is

< 50 is negative
= 50 is neutral
> 50 is positive

For example
Column A Column B
45 Negative
50 Neutral
80 Positive

View 4 Replies View Related

Inserting Row Based On Condition

May 4, 2009

I want to insert a line wherever I encounter a number. I have a range of cells in column "A" with invoices listed and at the end of each customer I have the total. At the total line (the number) I want to insert a line. Right now I can't figure out how get the cell value to recognize a number from a non-number data type (bolded area)

View 5 Replies View Related

Insert Row Based On Condition

Aug 12, 2009

I would like to write a code, that can check the name of cell A1 with A2.

If A1=A2, then insert a blank row between these two rows.

I have started with a loop, but somehow it does not work.

Sub AddRows ()

Dim Row As Long
For Row = 1 To 3800

If Cells(Row, 1).Value = Cells(Row + 1, 1) Then
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End If

Next Row

End Sub

View 9 Replies View Related

Delete A Row Based On Condition

Dec 14, 2009

I want to delete a row in excel based on a condition.

The condition is as follows:

I have few rows in the excel where the columns 7,12,13,23 are same and column 24 in one row has some number and in some other row the column 24 is blank with orange color filled(the colorindex for orange is 44).

In this case I want the row with orange color filled in column 24 to be deleted.

View 11 Replies View Related

Sum Row Based On Condition Refering To A Different Row

Dec 9, 2009

I have a problem with bringing up the data I need to cells in one row.

In row 1 I have dates:
23-Nov 24-Nov 25-Nov 26-Nov etc.

In row 2 Quantities:
2000 1500 250 750 etc.

In row 3 I have dates again (I need to start working towards these qty's): 20-Nov 22-Nov 25-Nov 25/Nov. I would like in column A of 4 row to sum all the qty's (row 2) if the date in row 3 is the same or smaller than in row 1 column A. But in column B and forward I want cells to sum qty's if the date in row 3 is the same as the one in row 1. I have tried : =sumproduct((A3:D3<=A1),(A2:D2)) for cell A4

and: =sumproduct((A3:D3=B1),(A2:D2)) for cell B4 and consequently for others. It comes back as zero value even if I see matching dates/qty's. Does anyone know what shall I change in my formula to make it right?

View 3 Replies View Related

How To Change One Condition Based On Another

Feb 2, 2007

I have a "if" statement that goes something like this:

=if(and(Function1),do this, if(and(Function2), do that, 0)

where Function 1 and Function 2 consist of three conditions each.

Now Function 2 has reference to a particular cell (U23), whose value is dependent on other cell references.

If U23 has some real value, I want the above statement to be executed.

However, if U23 displays a "#N/A", I want only Function1 to be executed. i.e. = if(and(Function1),do this,0)

View 9 Replies View Related

Subtotaling Based On A Condition

Sep 27, 2009

I autofilter a list which contains, among other fields, program numbers in column A, account numbers in column B and dollar amounts in column E. I want to display the filtered/subtotal of column E if I select a certain program. Each program has account numbers ranging from 1000 to 8999.

I used =SUM(IF()) formulas to separately display totals with account numbers below 8000, and >7999 with no problem. But when I got to create formulas to display the subtotal, I encountered difficulty.

I used the following CSE formulas to subtotal sum of column E based on the values in column B, the first formula return 0 (zero), which is incorrect. The second formula appears to ignore the IF statement, and returns the total sum instead of the filtered total.

1. {=IF($B$4:$B$112>7999,SUBTOTAL(9,E$4:E$112),0)}

2.{=IF($B$4:$B$112

View 8 Replies View Related

Maximum Based On Condition

Dec 28, 2006

function that can find the maximum value in a range, where the range is defined by the rows containing a specific string, i.e:

In column A:
Alm Rød
Alm Grøn
Alm Blå
Special Rød
Special Grøn
Special Sort
...

In Column B I have values for each of the above string names.

Now I would like to find the maximum value for the range with names starting with "Alm".

View 6 Replies View Related

Delete Row Based On Condition

Jan 19, 2007

i have an issue i need to delete entire row if a condition is met, i need syntax if possible to delete if a cell word "begins with" a certain letter ...in this case...letter A

column is "O"
must search threw entire row

View 3 Replies View Related

Count Total Based On Condition.

Jan 18, 2010

I need a formula that will return a total for the number of cases that have been closed within 6 months. Also I need a percentage total for number of cases that have been closed within 6 months. So the conditions are that the case has been closed and within 6 months.

I tried to use a IF formula that returned a 1 if within 6 months and a 0 if outside 6 months and then just SUM() the column, however for some reason that just returns 0. I am using Excel2003

View 4 Replies View Related

Filter Based On Multiple Or Condition

Nov 6, 2013

Is there a way to filter data based on multiple OR condition with a like parameter.

I have data which has near about 50000 rows and now i would like to filter on a header called "Activity Type" and would like to fetch result for a text which resembles "from ABC group" or "From PQR group" or "From XYZ group".

View 5 Replies View Related

How To Have One Cell Copy To Another Based On Condition

Mar 10, 2014

I'm trying to set it up so that if a particular cell has any data in it, the contents of a different cell get sent to another. I want the no data/data to serve as a trigger.

As an example, if I put anything into cell A3, I want the contents of cell A1 to copy to H1. I'd also like it to be dynamic so that if I put something into cell B3, the contents of B1 get copied to H1, etc.

View 4 Replies View Related

Insert Row And Values Based Upon Condition

Apr 4, 2014

I need a macro I can run that will insert a row whenever Column A changes (from PPN A to PPN B to PPN C, etc.) and that will insert string values into the Cells as follows:

In all cases, the insert cell values into column A (PPN) will be the value from the previous cell and column B (CPN) will be 'LABOR'

Here's image of before and desired after:

Before:

PPNCPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02ADMIX
4AQ02AUTUMN BLEND
4AQ02OVERHEAD
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03ADMIX
4AQ03BETHEL BLEND
4AQ03OVERHEAD

After:
PPN CPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02ADMIX
4AQ02AUTUMN BLEND
4AQ02OVERHEAD
4AQ02LABOR -insert row and populate cells here
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03ADMIX
4AQ03BETHEL BLEND
4AQ03OVERHEAD
4AQ03LABOR -insert row and populate cells here

View 1 Replies View Related

Autofill Based On Cell Condition

May 20, 2014

I have a workbook sheet change condition that, when triggered runs the following code (amongst others).

[Code] ......

What I would like is to amend the above (possibly in a loop?) so that it skips the rows in which the value "New" features in range F17:F190.

So basically Autofill as per the code but leave the values in the cells of the range in which New features in range F17:F190

View 4 Replies View Related

VBA To Delete Entire Row Based On Condition Met?

Jun 16, 2014

I have table in which against the Column A data values are added in Column B, C and D.

If Column B,C and D cell values is 0, then I want entire row including A to be deleted. How can I do this in VBA?

Note: Column A data begins from Row 15 and below.

Just to avoid confusion. I want row deletion, only when all the three cell on B,C and D is = 0

View 7 Replies View Related

Highlighting Range From A:I Based On Condition

Mar 14, 2014

I have a file in which I have data from A9:I50.

In the column C i have the type mentioned either MTC or SELF.

If the value in column C is SELF then I want to highlight the corresponding rows from A:I.

[Code] ......

This code is colouring the entire row.

I want it to be highlighted from A to I.

View 2 Replies View Related

Coloring Cell Based On Condition

Jan 18, 2009

I had an report and the rows of the report needs to be colored based on another cell value. My layout looks like this:

A1B1C1D1AVG
1011121330
14151617100
18192021400

For each cell, i need to compare the value of cell with that value in AVG coloumn and if condition is met, cell should be colored. Eg : If first cell value under A1 is Less than 0.2 * AVG of first row then color of the cell A2 should be red. After coloring the entire rows, then i should check and color the header row based on the color of Rows.

I tried first one, i,e coloring the rows based on conditional formatting and then written VB code to compare the color of rows and colored the header row. But the color index used in conditional formatting and the code are mismatching and the desired output is not coming.So decided to write code for coloring the rows cell.

View 4 Replies View Related







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