Hello,

How can I combine an IF statement and Vlookup function?

I need to create a nested funciton where if cell R3 contains the text "ER01"

then Vlookup list MTN_ER_T1.

I need to combine the 2 functions below:

=IF([Cell_Path_Assignments.xls]MTN_179_CD!R3="ER01"

then

=VLOOKUP(J2,Cell_Path_Assignments.xls!MTN_ER01_T1, 1,FALSE)

Thanks,

Jaret

How can I combine an IF statement and Vlookup function?

I need to create a nested funciton where if cell R3 contains the text "ER01"

then Vlookup list MTN_ER_T1.

I need to combine the 2 functions below:

=IF([Cell_Path_Assignments.xls]MTN_179_CD!R3="ER01"

then

=VLOOKUP(J2,Cell_Path_Assignments.xls!MTN_ER01_T1, 1,FALSE)

Thanks,

Jaret

- Combine If statement and Mid Statement
- How to do IF statements with VB functions
- If statement and match function problem
- XL97: How to Use the EFFECT and NOMINAL Functions to Calculate Loan or ...
- How to lookup multiple values and summing them up in one cell
- XL97: How to Use the EFFECT and NOMINAL Functions to Calculate Loan or ...
- How to lookup multiple values and summing them up in one cell
- IF Statement and Vlookup help
- How to combine IF, VLOOKUP and MATCH?
- Combine vlookup and copy function
- If statement and Vlookup combined
- Combining If-statement And Range
- Combining IF OR and AND functions
- Formula for multiple IF statements and LOOKUP functions
- Telling Excel where to get data without writing a whole bunch of IF statements
- Combining IF OR and AND functions
- How to combine 2 worksheets? Find duplicates without deleting rows or values?
- Change row color according to an if statement
- Combining if statements
- IF statements and Server Imported Data
- How to use 'If' statements for multiple possibilties
- How to combine chart type?
- Pulling data by name in column
- IF Statement and Vlookup combined

Trying to Combine If statement and a Mid statement in One Cell vs two separate cell.

Cell with text in it is D2--cell reads-- 001-012400-000-7010-000.

from this I used =mid(d2,5,6) returns 012400 - which was easy but I want IF that was true do this..

the if this true I want the formula to return the word "Location" if don't match then blank cell.

I wrote this - but doesn't work.

=If(MID(d2,5,6)="012400","Location"," ")

Any help would be great

thanks

Chris

I currently have a column with dates that change colour when it is 3 months away from the date (the font colour changes red from black)

and i also have a column which has a Ok/Renew selection and I want that when Ok is selected the dates go back to being coloured red and when Renew the dates that are within the 3 month period stay black.

I tried doing this with conditional formatting but it ended up looping and once the dates had turned red they stayed red even if the other column said yes.

I've had a try at doing it in VB but know its wrong, I'm not sure how to do a VB IF statement and how to put it in the cell and still keep the date in that cell.

Here's what i've attempted so far:

Function ColourChange(EndDate As Date, TodaysDate As Date, Action As String)

If (Action = "OK") And (TodaysDate - EndDate < 90) Then

ColourChange.Font.Color = vbRed

ElseIf (Action = "Renew") And (TodaysDate - EndDate < 90) Then

ColourChange.Font.Color = vbBlack

ElseIf (Action = "OK") And (TodaysDate - EndDate > 90) Then

ColourChange.Font.Color = vbBlack

ElseIf (Action = "Renew") And (TodaysDate - EndDate > 90) Then

ColourChange.Font.Color = vbBlack

End If

End Function

Hopefully this makes sense, if it doesn't I can try and go into more detail.

thanks

Here is my attempt but I got 0, it should be 50.

=IFERROR(VLOOKUP(B2:C2,Cost!A2:C8,MATCH(Main!B2:C2,Cost!A2:B7,0),FALSE),0)

... lower interest costs for the borrower in the Canadian situation, and illustrates the importance of knowing the financial environment in which you are using the worksheet functions.

a "Name" in a table and sum up the corresponding grades (multiple cells) in

another table. Can anyone help. Thanks

Financial institutions may calculate interest based on ... and illustrates the importance of knowing the financial environment in which you are using the worksheet functions.

a "Name" in a table and sum up the corresponding grades (multiple cells) in

another table. Can anyone help. Thanks

This is all fine but I would like to make everything one line for sorting purposes. I was wondering if this is possible with Vlookup or something else? I am not sure how to combine the formulas that are on two lines now into one line. I will still need it to function the same way. When ever I enter the letters A through S I still need the blade size to pop up but only on one line instead of two.

I hope this all making sense, I have attached a copy of the sheet to help clear things up.

Thank youBANDSAW BLADE ANALYSIS REPORT (BLANK) (D4.439).xls

If A7 is equal in any rows in sheet 2 column A, B6 is equal to any rows in sheet 2 column B, and A3 is equal to any rows in C, then return the value of sheet 2 column D.

Thanks!

worksheet for others where information can be selected by drop down lists.

Whatever they select will then call up addtional information via a vlookup.

But, in case the information they are looking for is not already in the list,

they need to be able to type it in - so I cannot protect those cells and do

not want to lose the formula for future use.

If I could put the formula somewhere else and include a 'copy to' command,

it would solve the problem.

Can this be done?

I'm running an if statement and if the result is true I'm then trying to get it to run a vlookup.

The false result works fine (that's simple anyway) but when it's true it just displays the vlookup text rather than the actual result.

=IF(B13<>"-","=VLOOKUP(B13,Results!$A$2:$V$25,4",0)

can it be done to display the result of the vlookup as on it's on it works as it should but within the if statement it doesn't.

Thanks in advance

Peter

Is it possible to combine IF OR AND functions? I can combine IF with

AND and IF with OR but not all three.

E.g. Is it possible to put the following into a formula:

'IF A2 AND A3 = A1 OR A4 AND A5 = A1 return yes

Hope you can help.

Many thanks

--

andyp161

------------------------------------------------------------------------

andyp161's Profile: http://www.excelforum.com/member.php...o&userid=11654

View this thread: http://www.excelforum.com/showthread...hreadid=534558

I am very pleased with the responses I have gotten from this forum in the past and have come across another roadblock in one of my spreadsheets.

Here's the scenario and logic:

IGNORE IN RED

1) The formula will use an IF statement to determine the answer to Question A is (Yes or No)

a) If Yes, the formula will automatically kick out a result of 0 and the formula is done.

b) If no we proceed to another IF statement.

2) The IF statement will then use a LOOKUP function (my opinion, unless a different approach is recommended) to pull a rate from a separate table on another sheet. There are 24 options available from a drop-down menu to determine the appropriate rate on the input screen.

3) We will then pull the value from an input field.

4) We will use another IF statement (Yes or No) to determine the answer to Question B.

a) If yes, we need to perform another LOOKUP to find an amount to subtract from the value BEFORE the calculation is performed.

b) If no we perform the calculation below.

5) VALUE minus AMOUNT from item #4, if applicable, multiplied by the RATE from item #2

6) We will then use an IF statement to determine the answer to Question C (3 answers from drop-down menu). The formula will kick out the calculation from #5 either divided by 0, 1, or 2.

Does this make sense? I can break this down further or share the spreadsheet if necessary.

spreadsheet that produces a share value based on Earnings Per Share in

a given year.

As it stands now, the spreadsheet and formulas are simple. The

"Valuation" page imports Earnings Per Share from another sheet in the

workbook, and everything on the "Valuation" page is hard-coded to look

in H13 for 2009 Earnings Per Share.

G13 has 2008 Earnings, H13 has 2009 Earnings, I13 has 2010 Earnings,

and so on.

The drawback with this approach is that the spreadsheet can only

calculate valuation based on 2009 earnings. If I want to use 2008, or

2010, or any other year, I have to rewrite a bunch of formulas. (I know

that I could Find->Replace, but this won't help the people in my office

who know even less than I know)

I realize that I could fix this by using a _whole_bunch_ of IF

statements--

For example (in English), I could tell the formula that IF A2 is 2004,

use the data in C13, IF A2 is 2005, use the data in D13, IF A2 is 2006,

use the data in E13...IF A2 is 2013, use the data in N13, and so on.

I have found references that explain how to nest IF statements and

combine others that have reached the nesting limit. Of course, this

seems like a reeaally stupid way to solve the problem.

In other words, I need to tell Excel that "If the user puts 2010 in

cell A2, use the figure in I13. For every number less, go left one

cell. For every number greater, go right one cell. Spit out the value

in D54."

I hope I've adequately explained the problem. Any help?

Is it possible to combine IF OR AND functions? I can combine IF with AND and IF with OR but not all three.

E.g. Is it possible to put the following into a formula:

'IF A2 AND A3 = A1 OR A4 AND A5 = A1 return yes

Hope you can help.

Many thanks

First thing I have to do is to merge the column A from excel file #2 to column A excel file #1 by their corresponding rows. Ex: excel file #2 row 1 to be combined to excel file #1 row1 in just one string. Once this is done I have to find if there are duplicates from one row to the others among different columns. There can be duplicates on the same row (horizontally) but not vertically.

Here are my questions: How to combine 2 worksheets and matching the row without creating empty cells? Once this is done how to find (highlighted or identity) duplicates form one row to the other among different strings without deleting the duplicates?

Thanks for any help.... hoping to get some...

I got a user that will paste information into an excel sheet.(sheet 1)

i have alreday recorded a macro (probably need to record a new one)

that copies the format from sheet 2 and paste it onto sheet one.

Sheet 2 is already row conditional formated to row 1000.

This works but its a bit slow.

So i was wondering to use something like this (see below).

But as im a newbie to programing so i dont know how to change the "Select

Case

LCase(Target.Value)" to an if statement and that the change macro should only

be applied up to and incl. the last row of information.

thank you,

Sten

---------------------------------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

'David McRitchie, 2004-09-26, programming, Case -- Entire Row

' http://www.mvps.org/dmcritchie/excel/event.htm#case

If Target.Column <> 6 Then Exit Sub 'Column F is column 6

If Target.Row = 1 Then Exit Sub

Application.EnableEvents = False 'should be part of Change macro

Select Case LCase(Target.Value)

Case "yes"

Target.EntireRow.Interior.ColorIndex = 34

Case "no"

Target.EntireRow.Interior.ColorIndex = 36

Case Else

Target.EntireRow.Interior.ColorIndex = xlColorIndexAutomatic

End Select

Application.EnableEvents = True 'should be part of Change macro

End Sub

----------------------------------------------------------------------------------------------

Please help with this:

i am trying to combine if statements to give me if sales is 102% above target it returns "Above target". if sales if between 98% to 102 % it returns "Achieved" and if sales is less than 98% than it returns "below target"

can someone please help

I am having another problem with excel 2007. We have a system at our company that automatically reports readings in the field to a historian server. We have excel workbooks that once opened, populate from automatically from the historian via the SQL database connection. However, our system is flawed and prone to sending erroneous data, in string form(ex. "***NO DATA***"), to the excel workbooks. We were trying to figure out how to use IF statements as a filter of sorts to elimate this from happening, but it seems IF statements don't like the comparison of strings and integers/doubles, which makes sense. My question to you is: how would you output a ZERO in the case IF a ***NO DATA*** string populates the workbook? Is it possible to do a quality check like this for all incoming data for the whole workbook? Or is there a disconnect between the two systems that will not allow us to do an automated quality check, which must be completed by hand?

Thanks again in advance y'all,

James -- The_Intern

2. My 'D' column cells require 'if' statements

And using this table:

1 < x < 5 in 'C' cells, 'D' cell in same row will be = C5*I2:

Use values:

I2 if 1 < x < 5,

I3 if 5 < x < 10

I4 if 10 < x < 15

etc.

I have 2 spreadsheets.

One has a column with ORG NAME and a SCORE #.

In the other, I want to calculate the average score of a certain ORG.

For example (spreadsheet 1):

Employee 1 Finance 40

Employee 2 Finance 45

Employee 3 Finance 43

Employee 4 Design 30

Employee 5 Design 34

In spreadsheet 2, I want to pull the AVERAGE score for all the employees in the FINANCE org only. So (40+45+43/3)

Can anyone help me with a formula here? I can't figure out how to combine IF, AVERAGE and VLOOKUP to get the result I want.

I am having problems trying to combine a Vlookup and If statement.

The formula I have written is: =IF(VLOOKUP(B386,Extensions!$A$4:$D$128,3,FALSE)="","",VLOOKUP(B386,Extensions!$A$4:$D$128,3,FALSE))

The problem is that where the Vlookup does not find the entry in B386 I am getting the error "N/A" appearing. This is correct because the value in B386 (for example) does not exist in the sheet I am looking up.

What I want to happen is that rather than "N/A" being shown I would like a blank space to be entered.

Can anyone tell me how I can force a blank space (hence why have used "" within my formula) rather than the default "N/A" which the vlookup throws up?

Regs

MZP